Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

onur0707
New Contributor III

pivot table cumulative sum

Hello everyone,

I am trying to calculate cumulative sum in a pivot table in Qlik Sense.

My row is Year; (2018,2017,2016...)

My columns are Month(Jan,Feb...) and Scenario_(Actual,Plan)

My measure is sum of Amount_

Data sample is below:

sample.PNG

What I want is, Amount_ values should be cumulatively summed in pivot table for the months.


For example: March 2016  sales values should be sum of Jan 2016 and Feb 2016 on pivot table and rest of them also should be the same.

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

For now, I have below pivot table.

Capture.PNG

Please suggest me any solution if anyone of you gone through the same problem. Any help will be appreciated.

Thanks.

OY

8 Replies
YoussefBelloum
Esteemed Contributor

Re: pivot table cumulative sum

Hi,

your expression is correct.

can you attach sample data ? the QVF ?

onur0707
New Contributor III

Re: pivot table cumulative sum

Hi,

result of "=RangeSum(Above(Sum({1}AMOUNT_), 0, RowNo()))" is below, it is not what I want.


Screenshot_1.png

sample data:

  

MONTHMONTH_NUMSCENARIO_YEAR AMOUNT_
Jan1Plan2018         2.102
Feb2Plan2018         2.102
Mar3Plan2018         2.102
Apr4Plan2018         2.102
May5Plan2018         2.102
Jun6Plan2018         2.102
Jul7Plan2018         2.102
Aug8Plan2018         2.102
Sep9Plan2018         2.102
Oct10Plan2018         2.102
Nov11Plan2018         2.102
Dec12Plan2018         2.102
Jan1Actual2018         2.270
Feb2Actual2018         2.270
Mar3Actual2018         2.270
Apr4Actual2018         2.270
May5Actual2018         2.270
Jun6Actual2018         2.270
Jul7Actual2018         2.270
Aug8Actual2018         2.270
Sep9Actual2018         2.270
Oct10Actual2018         2.270
Nov11Actual2018         2.270
Dec12Actual2018         2.270
Feb2Actual2017         1.775
Jan1Actual2017         1.800
Dec12Actual2017         1.812
Nov11Actual2017         1.886
Jan1Plan2017         1.975
Feb2Plan2017         1.975
Mar3Plan2017         1.975
Apr4Plan2017         1.975
May5Plan2017         1.975
Jun6Plan2017         1.975
Jul7Plan2017         1.975
Aug8Plan2017         1.975
Sep9Plan2017         1.975
Oct10Plan2017         1.975
Nov11Plan2017         1.975
Dec12Plan2017         1.975
Apr4Actual2017         2.008
Sep9Actual2017         2.035
Oct10Actual2017         2.038
Mar3Actual2017         2.122
Aug8Actual2017         2.139
Jul7Actual2017         2.228
Jun6Actual2017         2.541
May5Actual2017         2.586
Jan1Plan2016         1.481
Feb2Plan2016         1.481
Mar3Plan2016         1.481
Apr4Plan2016         1.481
May5Plan2016         1.481
Jun6Plan2016         1.481
Jul7Plan2016         1.481
Aug8Plan2016         1.481
Sep9Plan2016         1.481
Oct10Plan2016         1.481
Nov11Plan2016         1.481
Dec12Plan2016         1.481
May5Actual2016         1.681
Jan1Actual2016         1.686
Feb2Actual2016         1.829
Mar3Actual2016         1.902
Sep9Actual2016         1.929
Oct10Actual2016         1.946
Nov11Actual2016         1.949
Jun6Actual2016         1.999
Aug8Actual2016         2.130
Apr4Actual2016         2.147
Jul7Actual2016         2.194
Dec12Actual2016         2.311
Aug8Actual2015         1.237
Jul7Actual2015         1.539
Jun6Actual2015         1.550
Jan1Plan2015         1.584
Feb2Plan2015         1.584
Mar3Plan2015         1.584
Apr4Plan2015         1.584
May5Plan2015         1.584
Jun6Plan2015         1.584
Jul7Plan2015         1.584
Aug8Plan2015         1.584
Sep9Plan2015         1.584
Oct10Plan2015         1.584
Nov11Plan2015         1.584
Dec12Plan2015         1.584
Feb2Actual2015         1.616
Sep9Actual2015         1.693
Jan1Actual2015         1.716
Nov11Actual2015         1.721
Dec12Actual2015         1.977
Apr4Actual2015         2.057
Mar3Actual2015         2.063
Oct10Actual2015         2.088
May5Actual2015

         2.196

regards;

OY.

YoussefBelloum
Esteemed Contributor

Re: pivot table cumulative sum

here I applied your expression with your data and it seems good.

screen rangesum.png

I think your problem is related to the type of sorting of the field Year (asc/desc).

If you put the sorting of the year on descending, it will start to calculate the range from the latest year.

you need maybe to put the sorting on ascending ?

onur0707
New Contributor III

Re: pivot table cumulative sum

Hi, I think I have fixed it, I have add one more measure

now two measures like below:

for Actuals:

sum({<MONTH={"<=$(=max(MONTH))"}>}aggr(rangesum(above(Sum({<MONTH=,YEAR=,SCENARIO_={'Actual'}>}AMOUNT_),0,RowNo())),YEAR,(MONTH,(num))))

for plans:

sum({<MONTH={"<=$(=max(MONTH))"}>}aggr(rangesum(above(Sum({<MONTH=,YEAR=,SCENARIO_={'Plan'}>}AMOUNT_),0,RowNo())),YEAR,(MONTH,(num))))

result is below:Screenshot_2.png

YoussefBelloum
Esteemed Contributor

Re: pivot table cumulative sum

I don't see here the cumulative values related to your data structure and values.. but if the problem is solved for you don't hesitate to close the thread

kaanerisen
Contributor III

Re: pivot table cumulative sum

Hi Onur,

You just need to use RangeSum(Before(Sum(AMOUNT_), 0, ColumnNo())) as measure on your pivot table. If you want to get running sum on horizantal, you have to use Before() instead of Above()  and ColumnNo() instead of RowNo(). Above() and RowNo() can be used for vertical running sums.

For example: March 2016  sales values should be sum of Jan 2016 and Feb 2016 on pivot table and rest of them also should be the same.

I am not pretty sure about your actual need but if you want to show sum of Jan and Feb on Mar Column (except Mar value). you need to

edit this expression.

RangeSum(Before(Sum(AMOUNT_), 1, ColumnNo()))

Untitled.png

Untitled.png

Hope it helps,

Kaan ERİŞEN

onur0707
New Contributor III

Re: pivot table cumulative sum

Hi Kaan;

Thank you for your kind help.

Your code is working if the year values are in columns.

For my case, month values should be in colomn and year values should be in row.

Provided code 'RangeSum(Before(Sum(AMOUNT_), 0, ColumnNo())) ' not working for this case.

regards;

Onur

kaanerisen
Contributor III

Re: pivot table cumulative sum

How about this?

Measure : sum(aggr(rangesum(above(Sum(AMOUNT_),0,RowNo())),SCENARIO_,YEAR,MONTH))

Untitled.png