Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have data structured as the below
Weekstart | Day | Sales |
3/30/2020 | 3/30/2020 | 6,621,339 |
3/30/2020 | 3/31/2020 | 25,754,389 |
3/30/2020 | 4/01/2020 | 316,020,608 |
3/30/2020 | 4/02/2020 | 4,591,320 |
3/30/2020 | 4/03/2020 | 1,504,342 |
3/30/2020 | 4/04/2020 | 1,485,091 |
4/5/2020 | 4/05/2020 | 1,377,793 |
4/5/2020 | 4/06/2020 | 4,795,397 |
4/5/2020 | 4/07/2020 | 1,128,845 |
4/5/2020 | 4/08/2020 | 1,857,699 |
4/5/2020 | 4/09/2020 | 1,419,651 |
4/5/2020 | 4/10/2020 | 5,436,237 |
4/5/2020 | 4/11/2020 | 14,677,500 |
On my sheet I want to create a Pivot Table with the Dimensions Weekstart and Day as columns and the measures sum(Sales) and "Cumulative Sales" as rows. For "Cumulative Sales" I am using the formula
Rangesum(Sum(Sales),Before(Sum(Sales),1,ColumnNo()))
This is working fine when I have the Weekstart dimension fully collapsed
However when I expand one of the Weekstart's then Cumulative works for the days within that week but does not work for the next Weekstart. In the below I would expect the value for 4/5/2020 to still be 386,670,214 not 30,363,124
Does anyone have any ideas on how to accomplish this?
I've attached the source data and QVF if anyone wants to take a look
Thanks!
Mark
See if this works for you
RangeSum(Sum(Sales), Before(TOTAL Sum(Sales), 1, ColumnNo(TOTAL)))
See if this works for you
RangeSum(Sum(Sales), Before(TOTAL Sum(Sales), 1, ColumnNo(TOTAL)))
Perfect thank you!!
I have a pivot table with 3 dimensions (Year, Quarter, Month) and calculating cumulative sum of sales using below expression
RangeSum( Above(TOTAL Sum(Sales), 1, Rowno(TOTAL)))
Cumulative sum is working fine when dimensions are fully expanded and not giving correct result when its collapsed.