Enterprise Analytics: Cheat Sheet - Running Sum Metrics

  • 0
  • 1
  • Article
  • Updated 4 years ago
  • (Edited)
Archived and Closed

This conversation is no longer open for comments or replies and is no longer visible to community members. The community moderator provided the following reason for archiving: WF Sunset

Background:
In the steps below, you will learn how to create and utilize a Running Sum Metric.  Building on your skills with derived metrics (such as Ranking metrics and Percent-to-Total metrics), a Running Sum metric helps you determine the percentage of sales the top XX doors contribute, or which doors make up the top XX% of sales.

The Running Sum metric adds each value in a list of values, up to and including the current value (in this example, % to TTL based upon Unit SIs YTD).  The last value in the list is the total contribution of the running sum.  Applying a view filter helps narrow the Running Sum down to points of interest - such as 'What % of annual sales do my top 10 stores contribute?' 'What stores make up 25% or 50% of my total annual sales?'

Inserting a Running Sum Metric
1.  Right-click on the metric name you wish to create a running sum for.  Click Insert Metric → New

2.  Name your metric - Running Sum, or as desired

3.  Either click the fx button to access the RunningSum calculation, or copy and paste the Running Sum formula in the next section.

                                                  

4.  Select apply to preview your Running Sum

5.  Select OK to save to the grid

6.  Left-click on the Running Sum values.  Click the Format tab → Advanced Formatting → Number → Change to Percentage with 2 decimal places.  Hit OK to save.



Example of Report with a Running Sum:
In this example, we see that the top 12 stores within Action Sports are responsible for almost 50% of annual unit sales.

                                

Copy and Paste Formula for Running Sum:
1.  Generic Formula to Copy and Paste:

            RunningSum<BreakBy, SortBy> (Value List)

**BreakBy stands for the level at which the ranking is performed; ASC stands for Ascending.
  • Your ValueList is what you want to add up in the Running Sum
  • You may or may not need a BreakBy - it determines the groups over which the function is calculated
  • Your SortBy may auto populate and you'll likely want to select another option - it determines how data is sorted within each group when the function is calculated

Running Sum Example with Formula:
1.  Running Sum of % of Total Sales - based on % TTL of TY Unit Sls YTD by Door within a Retailer
  • Our ValueList is % of Total (a % TTL derived metric based upon Unit Sales: [TY YTD UNITS SLS] / Sum([ TY YTD UNITS SLS]){Retailer}
  • BreakBy is left blank
  • SortBy is adjusted to Rank (TY Unit Sales) - a derived metric at the door level by retailer:  Rank,ASC=False, BreaBy = {Retailer}>([TY YTD UNITS SLS])
                                              

2.  Formula: RunningSum<SortBy = ([Rank (TY YTD UNIT SLS) Break by (Retailer) 1])>([Percent to Total (TY YTD UNITS SLS)])

**Note the insert of the 1 to ensure the proper level of additional of the % to TTL.

Using View Filters with Running Sum Metrics:
1.  Turn on View Filter form Tools | View Filter

2.  Click 'Add Condition':
  • Select your Running Sum metric
  • Add the appropriate condition such as less than or equal to 0.25 to view the doors that account for 25% of the total unit sales for the year.
                                          
Photo of FAQ

FAQ, SPS FAQ

  • 9,340 Points 5k badge 2x thumb

Posted 4 years ago

  • 0
  • 1

There are no replies.

This conversation is no longer open for comments or replies.