Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor

How to calculate cumulative sum in a pivot table

Hello everyone,

I am trying to calculate cumulative sum in a pivot table. But I am not able to get the exact values.

I have tried "=RangeSum(Above(Sum(Sale), 0, RowNo()))" but it did not worked.

Please suggest me any solution if anyone of you gone through the same problem.

Suppose I have below data structure:

  

FYJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
2012          0,98           1,08           1,06           0,97           1,62           1,45           1,54           0,90           1,25           1,32           1,25           1,07
2013          1,04           1,17           1,32           1,61           1,64           1,40           1,73           1,54           1,56           1,47           1,72           2,47
2014          1,63           1,50           1,86           1,75           2,11           1,77           1,45           1,37           1,83           1,99           2,43           1,85
2015          1,74           1,64           2,09           2,08           2,23           1,57           1,56           1,25           1,72           2,12           1,74           2,00
2016          1,71           1,85           1,93           2,18           1,70           2,03           2,22           2,16           1,95           1,97           1,97           2,34
2017          1,82           1,80           2,15           2,03           2,62           2,57           2,26           2,17           2,06           2,07           1,91           1,84
2018             -                -                -                -                -                -                -                -                -                -                -                -  


On Qlik Sense, I have below pivot table dashboard: 

Fiscal YearJanFebMarAprMayJunJulAugSepOctNovDec
20180,000,000,000,000,000,000,000,000,000,000,000,00
20171,821,802,152,032,622,572,262,172,062,071,911,84
20161,713,651,932,181,702,032,222,161,951,971,972,34
20151,745,292,092,082,231,571,561,251,722,121,742,00
20141,636,791,861,752,111,771,451,371,831,992,431,85
20131,047,961,321,611,641,401,731,541,561,471,722,47
20120,989,041,060,971,621,451,540,901,251,321,251,07

What I want is, Fiscal year month values should be cumulatively summed.


Ex: April 2017 value should be = 1,82+1,80+2.15+2,03 = 7,81

      Mar 2017 value should be = 1,82+1,80+2.15= 5.77

Please suggest any appropriate solution...

Thanks.

OY

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: How to calculate cumulative sum in a pivot table

If you have the in-memory table exactly like you descibe in a crosstable fashion and hasn't used the CrossTable prefix in a load to unpivot the data then this would be a way of getting what you want:

2018-01-11 17_26_12-QlikView x64 - [C__Users_Petter_Downloads_# QC 2018-01-11 Pivot cummulative sums.png

This works equally well with a straight table as with a pivot table.

However I would suggest that you unpivot your table in your load script with a CrossTable prefix and then Sunny's suggestion would be the right approach and then you must use a pivot table to visualize it in your app.

View solution in original post

5 Replies
Highlighted

Re: How to calculate cumulative sum in a pivot table

For pivot table with pivoted dimension  (Month) you need to use Before and ColumnNo() instead of Above and RowNo() functions

=RangeSum(Before(Sum(Sale), 0, ColumnNo()))

Highlighted
MVP
MVP

Re: How to calculate cumulative sum in a pivot table

If you have the in-memory table exactly like you descibe in a crosstable fashion and hasn't used the CrossTable prefix in a load to unpivot the data then this would be a way of getting what you want:

2018-01-11 17_26_12-QlikView x64 - [C__Users_Petter_Downloads_# QC 2018-01-11 Pivot cummulative sums.png

This works equally well with a straight table as with a pivot table.

However I would suggest that you unpivot your table in your load script with a CrossTable prefix and then Sunny's suggestion would be the right approach and then you must use a pivot table to visualize it in your app.

View solution in original post

Highlighted
MVP
MVP

Re: How to calculate cumulative sum in a pivot table

Please close this thread and mark it as answered if it indeed did so for you - if not please ask more to conclude the thread.

Highlighted
Contributor II

Re: How to calculate cumulative sum in a pivot table

Can you please send the solution again?
Highlighted
New Contributor

Re: How to calculate cumulative sum in a pivot table

Hi

Thank you very much for sharing, you solved a big problem.

best regard