How to calculate cumulative sum in a pivot table

[L_Hop]

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:

FY	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
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 Year	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2018	0,00	0,00	0,00	0,00	0,00	0,00	0,00	0,00	0,00	0,00	0,00	0,00
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
2016	1,71	3,65	1,93	2,18	1,70	2,03	2,22	2,16	1,95	1,97	1,97	2,34
2015	1,74	5,29	2,09	2,08	2,23	1,57	1,56	1,25	1,72	2,12	1,74	2,00
2014	1,63	6,79	1,86	1,75	2,11	1,77	1,45	1,37	1,83	1,99	2,43	1,85
2013	1,04	7,96	1,32	1,61	1,64	1,40	1,73	1,54	1,56	1,47	1,72	2,47
2012	0,98	9,04	1,06	0,97	1,62	1,45	1,54	0,90	1,25	1,32	1,25	1,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

[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 · Accepted solution]

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:

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]

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]

Can you please send the solution again?

[PASTORGAA]

Hi

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

best regard