Show Rolling 12 month sum, but only for selected months

[bediehau]

Hi community,

I have a chart (straight table) where I want to show the rolling 12 months Revenue.

The Chart shows OrderYearMonth with R12M next to it

This works just great with the following expression:

=RangeSum(Above(total Sum({<OrderYear={$(=Concat(Year,','))}, OrderMonth={$(=Concat(DISTINCT Month,','))},  OrderYearMonth=>}NetSalesValue)+ Sum({1} 0),0,12))

However: when the user selects a month (or a number of months) from the filter listbox, the values are wrong.
This is logical as the selected context changes and the unselected months are no longer in context.
I solved this by changing the set assignment for OrderMonth such that it ignores the month selection:

=RangeSum(Above(total Sum({<OrderYear={$(=Concat(Year,','))}, OrderMonth=, OrderYearMonth=>}NetSalesValue)+ Sum({1} 0),0,12))

Unfortunately, although the values are now correct (the sum is taken over the past 12 months, and not only over the selected months that fall into the past 12 months), it is now showing table rows for every single month, not only for the selected months.

If sample data (partial) is:

201409    ....

201410    110

201411    105

201412     90

201501    100

201502      80

201503    110

201504    115

201505    110

201506    120

201507    115

201508    125

201509    120

201510    125

201511    115

Then resulting data (partial) is:

201508    ...

201509   1300   (120+125+115+120+...+110)  (201509 thru 201410)

201510   1315   (125+120+125+115+...+105)  (201510 thru 201411)

201511   1325   (115+125+120+115+...+90)  (201511 thru 201412)

So far, it all works great.

But if the user selects months from the filter (e.g. oct + nov) then the report should only show the 2 lines for those months, with the Rolling sum as shown above (not just the sum of oct + nov).
At this point, I can only get it to show either all months correctly, or the selected months incorrectly.

Notes:

* the "+Sum({1} 0)" is to capture months without data (otherwise the 'above' function would read too far back in the data)

* the set analysis parts for 'Concat(Year)' and 'Concat(Month)' are there because the filter listbox on year and month is not joined to the fact table. Depending on the information shown, it can refer to different dates (invoice date, order date, dispatch date, ....) so it is a data island.

Any hints on how I can get my straight table chart to show records only for the selected years & months, but still roll the sum over 12 previous months?

[bediehau]

So I have been reading up on Aggr() function, and actually came up with a solution myself. Yeah!

* first of all, my initial set analysis expression was NOT correct.
  it worked for the last year in the selection if I selected two consecutive years, but when I selected only 1 year, the R12M value started at 0 instead of calculating the sum for the prior 12 months. This makes sense, because the records of the prior year were not included in the set.

There was a simple solution to that: bring more years into the set (or well: go back 1 more year than selected)

RangeSum(Above(total Sum({<OrderYear={$(=Concat(Year,','))}+{$(=Concat(Year-1,','))}, OrderMonth=, OrderYearMonth=>}NetSalesValue)+ Sum({1} 0),0,12))

The '+' on the set analysis combines the sets

* next, as I was reading up on Aggr(), I understood that Aggr() creates a sort of temporary table, and on the result of that, again a function, and more importantly, an outer set analysis can be applied.

So I tried to do this:

=Aggr(RangeSum(Above(total Sum({<OrderYear={$(=Concat(Year,','))}+{$(=Concat(Year-1,','))}, OrderMonth=, OrderYearMonth=>}NetSalesValue)+ Sum({1} 0),0,12)), OrderYearMonth)

and it came back with exactly the same results as what I had previously, so the aggregation seemed to give me a equivalent result to what I already had.

* Time to try some outer set analysis on this 'temporary table':

=Sum( {<OrderYear={$(=Concat(Year,','))}, OrderMonth={$(=Concat(DISTINCT Month,','))}>} Aggr(RangeSum(Above(total Sum({<OrderYear={$(=Concat(Year,','))}+{$(=Concat(Year-1,','))}, OrderMonth=, OrderYearMonth=>}NetSalesValue)+ Sum({1} 0),0,12)), OrderYearMonth))

and oh wonderfully: it works!

it does exactly what I need.

Contrary to what I expected (in using only the OrderMonth dimension for my Aggr) it even responds to context, when filtering the data for SalesRep or Product Groups.

Now on to the next hurdle: how will it do when there's 10 million data rows involved?

I hope it may help another in the future.

[bediehau]

And so more test results:

whoever tricked me into thinking that "+Sum({1} 0)" was going to solve issues with missing months: you got me.

It doesn't do a thing: if months are missing in the data, the end result is wrong. unpredictably wrong even.

Just so you know...