Question
Samuel Claiborne · Dec 28, 2016

Weighted Sales Performance Metric

I am trying to create a query that returns the best and worst performing products for a given customer, based on this year's net sales versus last year's net sales, weighted by the total net sales for all of the products sold to this customer in the last two years.

I have created Last Year Net Sales (up to the last month end): AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-13]),measures.[Net Sales])

This year Net Sales: AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-1]),measures.[Net Sales])

Percent change: ((AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-1]),measures.[Net Sales]) - AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-13]),measures.[Net Sales])) /AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-13]),measures.[Net Sales])) * 100

Along with the product#, this gives me Four columns. In this example,

Product #     Last Year to Date Net Sales                 This Year to Date Net Sales                      % Change

A                                                                 763.75                                                               5.60                            (99.27)

B                                                        192,678.05                                                    10,179.27                            (94.72)

C                                                             1,070.85                                                          315.25                            (70.56)

D                                                                646.10                                                          197.10                             (69.49)

E                                                                 121.10                                                          430.55                             249.76

F                                                           24,258.17                                                    98,987.80                             299.81

etc.

Obviously, although product 'A' has experienced a bigger loss of sales, year over year as a percentage, the sales drop for product 'B' is much more concerning, since it represents the bulk of all declining sales for this customer. Similarly, Product F's increase in sales is far, far ,more important than product 'E's. I'd like to show the 20 worst performing products, year over year, for this customer, and the 20 best. Once I completed this, I'd want to take this logic and create another query that would show the 20 worst performing customers, and 20 best, and also, a 3rd query that would show the best and worst performing products at the entire company level.

But for now, I want to merely weight the sales change of each product I've sold this customer based on the total sales for this product, for this customer, against the total sales of all products, for this customer.

So, I want to add a fifth column. It takes the total sales for this product for both years on each row, divides by  the grand total sales for all products for those to years (329,653.59) and then multiplies by the percent change, which I think will give me a useful weighting (I am open to other suggestions).

Formatted to round, this gives us:

Product #     Last Year to Date Net Sales                 This Year to Date Net Sales                      % Change       Weighted (rounded)

A                                                                 763.75                                                              5.60                           (99.27)                                       (0.23)

B                                                        192,678.05                                                   10,179.27                            (94.72)                                    (58.29)

C                                                             1,070.85                                                          315.25                            (70.56)                                     (0.30)

D                                                                646.10                                                          197.10                             (69.49)                                    (0.18)

E                                                                 121.10                                                          430.55                             249.76                                       0.42

F                                                           24,258.17                                                    98,987.80                             299.81                                    112.1

so:

1) How do I do this? I was trying to do the first part: to get total net sales for all products for the last 2 years by creating a calculated measure that does this:

 AGGREGATE(%TIMERANGE([Invoice Date].[H1].[Month].&[NOW-13],[Invoice Date].[H1].[Month].&[NOW-1]),MEASURES.[Net Sales]) 

I wanted it to jump up my product hierarchy from this particular product to my all products level and sum the sales, but it's not working - it's returning numbers that make no sense to me..

2) The percent formula essentially replicates the aggregation math of the other two formulas, which means we're doing the work three times. With the addition of this new column, that'd be four times. Is there any way to do each calculation once and then use that result for the next calculation to increase efficiency, because this is a very expensive query:

WITH 
  MEMBER [Invoice DATE].[LYPTD] AS 'AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-13]))' 
  MEMBER [Invoice DATE].[PTD] AS 'AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-1]))'
  MEMBER [MEASURES].[TYPTDM] AS 'AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-1]),measures.[Net Sales])'
  MEMBER [MEASURES].[LYPTDM] AS 'AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-13]),measures.[Net Sales])'
  MEMBER [MEASURES].[PTDMPCT] AS '((AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-1]),measures.[Net Sales]) - AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-13]),measures.[Net Sales])) /AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-13]),measures.[Net Sales])) * 100'
  MEMBER [MEASURES].[PTDMDELTA] AS '(AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-1]),measures.[Net Sales]) - AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-13]),measures.[Net Sales])) '
  MEMBER [MEASURES].[SamtestWeight] AS '(((AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-1]),measures.[Net Sales]) - AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-13]),measures.[Net Sales])) /AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-13]),measures.[Net Sales])) * 100) * (AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-1]),measures.[Net Sales]) + AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-1]),measures.[Net Sales]))'
  MEMBER [MEASURES].[All_Prod_Net_Sales] AS 'AGGREGATE(%TIMERANGE([Invoice Date].[H1].[Month].&[NOW-13],[Invoice Date].[H1].[Month].&[NOW-1]),MEASURES.[Net Sales])'
SELECT NON EMPTY {%label([MEASURES].[LYPTDM],"Last Year to Date Net Sales",""),
       %label([MEASURES].[TYPTDM],"This Year to Date Net Sales",""),
       [MEASURES].[PTDMPCT],
       [MEASURES].[ALL_PROD_NET_SALES]}
ON 0,
       NON EMPTY order([Cust].[H1].[Customer].MEMBERS,measures.[PTDMPCT],BASC)
ON 1
FROM   [INVOICESFLAT]

Thanks in advance!

0
0 274
Discussion (3)3
Log in or sign up to continue

Hello Samual,

I am uncertain if I completely understood your problem but here are some thoughts:

1) Why are you working with month here if you are trying to calculate the total sales of the last two years?

I would suggest to use years instead:

AGGREGATE(%TIMERANGE([DateOfSale].[Actual].[YearSold].&[NOW-2]),[Measures].[Amount Sold])

If you want to calculate it for all products the wrap it in a %MDX statement and set %CONTEXT to columns:

%MDX("SELECT AGGREGATE(%TIMERANGE([DateOfSale].[Actual].[YearSold].&[NOW-2]),[Measures].[Amount Sold]) on 0 FROM [HOLEFOODS]","%CONTEXT","columns")

2) To avoid recalculating the same values you can use the %CELL function. For example %CELL(-2,0) will pick the value from the current row but two columns to the left.

Thanks for your answer!

My replies to your questions:

1) I am working with the month because when you're doing year-over-year comparisons, you need comparative data. What if it's February, and I am comparing jan-feb of this year with all of last year? Similarly, what if it's December 3rd, and I am comparing jan-dec of this year, missing almost all of December's sales, with all of last year? That is why I use last month as the cutoff, to insure a fair comparison. If I am running it in January of 2017, for example, it will run for jan-dec of 2016 and compare that with jan-dec of 2015.

However, once I got a semi-solution working (using %MDX, as you'd suggested) I tried to improve performance by making a higher-level cube, where the data was aggregated by customer, by product, and all on a single day per month, the first of the month, which obviously reduced the number of records for each customer for each product massively, as in the  detail cube, there is a row for every single LINE of every single invoice! But performance is still underwhelming, and I am now investigating using SQL KPIs against the base tables. I think this is one of those areas where SQL may outshine MDX.

1a) I did 'solve' the issue exactly as you said, wrapping an mdx 'subquery' via the %MDX statement, and, yes, using context on columns. It works, but it's SLOW... Even for a single, small customer with only a handful of sales per year.

2) I have used cellzero previously, but I am not fond of it, because it limits functionality to a specific query, and to that query's layout  - a layout I may not even want to use - some columns needed for the calculation are columns that may screw up a chart in a dashboard. I much prefer to build flexible, reusable parts. I wish that the MDX execution engine were smart enough, as some SQL engines are, to see if the same stuff is selected more than once, and if so, to save it in a variable array for reuse.

Thanks for your thoughtful answers!

1) There are cases where SQL works more effective than MDX. It all depends on the data models and the amount of the data. Optimization in MDX is also different from the optimizations in SQL and fits the typical use cases. Improving performance of MDX is on our Top-List for the next releases.

2) Using %CELL is not always the best solution. But in cases where you know the layout of the table and need values from other cells then this is the most effective way. But it is lacking flexibility. I just wanted to show you the options you have and you have to choose what is the best way for you.

Besides that we are having caching mechanisms and I expect that we do not repeat all calculations.

You can use the DeepSee Shell to check how effective that is for your query. In terminal:

SAMPLES>do $System.DeepSee.Shell()
DeepSee Command Line Shell
----------------------------------------------------
Enter q to quit, ? for help.
>>stats on
Stats are: on
>>SELECT NON EMPTY NONEMPTYCROSSJOIN([DateOfSale].[Actual].[YearSold].Members,[MEASURES].[TEST]) ON 0,NON EMPTY [Product].[P1].[Product Category].Members ON 1 FROM [HOLEFOODS] %FILTER %OR({[DATEOFSALE].[ACTUAL].[YEARSOLD].&[2015],[DATEOFSALE].[ACTUAL].[YEARSOLD].&[2016]})

                                Test                 Test
1 Candy                      2.583,44             3.425,33
2 Cereal                     2.583,44             3.425,33
3 Dairy                      2.583,44             3.425,33
4 Fruit                      2.583,44             3.425,33
5 Pasta                      2.583,44             3.425,33
6 Seafood                    2.583,44             3.425,33
7 Snack                      2.583,44             3.425,33
8 Vegetable                  2.583,44             3.425,33
============================================
Query Statistics:
 Results Cache:                        0
 Query Tasks:                          1
 Computations:                        16
 Cache Hits:                           0
 Cells:                                0
 Slices:                               1
 Expressions:                         48
 Join Indices:                         0
 
 Prepare:                          2,016 ms
 Execute Axes:                   229,250 ms
  Columns:                         7,567 ms
   Crossjoin:                      0,712 ms
   Members:                        5,519 ms
  Rows:                          218,790 ms
   Members:                      216,448 ms
  Execute Slicer:                  0,495 ms
 Execute Cells:                   29,594 ms
 Consolidate:                     97,073 ms
 Total Time:                     357,933 ms
 
ResultSet Statistics:
 Cells:                               16
 Parse:                            6,093 ms
 Display:                          4,503 ms
 Total Time:                      10,596 ms
---------------------------------------------------------------------------
Elapsed time:       .555019s

----------------------------------------

Run the query twice and look at the stats for cache.