Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
mjperreault
Creator
Creator

Cumulative Sum with Two Dimensions in a Pivot Table

Hi All,

I have data structured as the below

WeekstartDaySales
3/30/20203/30/20206,621,339
3/30/20203/31/202025,754,389
3/30/20204/01/2020316,020,608
3/30/20204/02/20204,591,320
3/30/20204/03/20201,504,342
3/30/20204/04/20201,485,091
4/5/20204/05/20201,377,793
4/5/20204/06/20204,795,397
4/5/20204/07/20201,128,845
4/5/20204/08/20201,857,699
4/5/20204/09/20201,419,651
4/5/20204/10/20205,436,237
4/5/20204/11/202014,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

Collapsed.PNG

 

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? 

2020-03-30 18_37_21-Qlik Sense Desktop.png

 

I've attached the source data and QVF if anyone wants to take a look

Thanks!
Mark

Labels (4)
1 Solution

Accepted Solutions
sunny_talwar

See if this works for you

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

View solution in original post

4 Replies
mjperreault
Creator
Creator
Author

@sunny_talwar ,


Was wondering if you had any thoughts.

 

Thanks,
Mark

sunny_talwar

See if this works for you

RangeSum(Sum(Sales), Before(TOTAL Sum(Sales), 1, ColumnNo(TOTAL)))
mjperreault
Creator
Creator
Author

Perfect thank you!!

BabyyGurusamy
Contributor
Contributor

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.