Using RangeSum to calculate a rolling total in a multidimension table
In a single dimensional chart, for example, bar chart, line chart, or straight table, the usual way to create a rolling total is to use the accumulate option in the expression. This doesn't work correctly in a multi-dimensional situation.
In this recipe, we will look at creating accumulations by using the RangeSum function.
Getting ready
Load the following script:
LOAD * INLINE [ Country, City, Sales, Orders USA, San Diego, 24567, 546 USA, Dallas, 54962, 345 USA, New York, 67013, 678 USA, Boston, 45824, 365 UK, London, 64002, 743 UK, Birmingham, 44291, 572 UK, Manchester, 40320, 534 Germany, Berlin, 52912, 643 Germany, Frankfurt, 61832, 678 Germany, Munich, 35812, 325 Japan, Tokyo, 42137, 562 Japan, Yokohama, 55832, 753 Japan, Osaka, 37643, 418 ];
How to do it...
Use these steps to calculate the rolling totals with RangeSum:
Create a new pivot table with Year and Country as dimensions.
Add the following...
