Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
L_Hop
Creator
Creator

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
petter
Partner - Champion III
Partner - Champion III

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
sunny_talwar

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()))

petter
Partner - Champion III
Partner - Champion III

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.

petter
Partner - Champion III
Partner - Champion III

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.

Sameer9585
Creator II
Creator II

Can you please send the solution again?
PASTORGAA
Contributor II
Contributor II

Hi

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

best regard