topic Cumulative Sum with Two Dimensions in a Pivot Table in App Development

Cumulative Sum with Two Dimensions in a Pivot Table

mjperreault — Sat, 16 Nov 2024 02:50:15 GMT

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

Re: Cumulative Sum with Two Dimensions in a Pivot Table

mjperreault — Wed, 01 Apr 2020 18:12:09 GMT

@sunny_talwar ,

Was wondering if you had any thoughts.

Thanks,
Mark

Re: Cumulative Sum with Two Dimensions in a Pivot Table

sunny_talwar — Wed, 01 Apr 2020 19:03:03 GMT

See if this works for you

RangeSum(Sum(Sales), Before(TOTAL Sum(Sales), 1, ColumnNo(TOTAL)))

Re: Cumulative Sum with Two Dimensions in a Pivot Table

mjperreault — Wed, 01 Apr 2020 19:10:35 GMT

Perfect thank you!!

Re: Cumulative Sum with Two Dimensions in a Pivot Table

BabyyGurusamy — Mon, 28 Sep 2020 16:33:18 GMT

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.