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

cumulative sum in bar chart

Hello everyone,

I am trying to calculate cumulative sum in a bar chart in Qlik Sense which has two dimensions; month and year. My measure is sum of Sales.

What I want is, sales values should be cumulatively summed in bar chart for the selected 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.

Right now I have below bar chart, it is only showing the related month year sales data.


bar chart.PNG

I have tried "=RangeSum(Above(Sum(Sales), 0, RowNo(total)))" but it has not worked. Result of the "=RangeSum(Above(Sum(Sales), 0, RowNo(total)))" is below picture.

rangesum_above_rowno().PNG

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

Thanks.

OY

30 Replies
OmarBenSalem

already told him to do so; but It STRANGERLY does not appear the way it should be..

aggr(rangesum(above(Sum(Sales),0,RowNo())),SecondDim,FirstDim)


sunny_talwar

I think you mentioned Second and First dimension, which isn't very clear.... should have just used Year, Month because we can see the names of the dimension from the image . I don't see why it would not work...

OmarBenSalem

I think this should work :

sum({<Month={"=$(=max(Month))"}>}aggr(rangesum(above(Sum({<Year,Month>}Sales),0,RowNo())),Year,Month))

Result:

Capture.PNG

and when we select a Month:

Capture.PNG

OmarBenSalem

Maybe .. Cause I was giving him an exp per analogy with other dimensions I was using; my bad

L_Hop
Creator
Creator
Author

thank you for your efforts but all the suggested codes not worked.

When I have used the below code, the output of is:

sum({<Month={"=$(=max(Month))"}>}aggr(rangesum(above(Sum({<Year,Month>}Sales),0,RowNo())),Year,Month))

final3.PNG

I have only two dimesion, first one month, second one Year.

year values are: ac15 - actual 2015

                         : ac16

                         : ac17

                         : ac18

                         : op18 - plan 2018

ac18 only has january data, all the other years have the whole month data.

month values are: Jan, Feb etc.

measure is sum of sales


regards;

OY

sunny_talwar

Give this a shot

Aggr(RangeSum(Above(Sum(Sales), 0, RowNo())),Year, (Month, (NUMERIC)))

L_Hop
Creator
Creator
Author

there is no numeric function in Qlik sense and I have used NumericCount function and it has worked, Thank you.

Aggr(RangeSum(Above(Sum(Sales), 0, RowNo())),Year, (Month, (NumericCount)))


regards;

OY

sunny_talwar

NUMERICCOUNT? Really? I don't even know what that does... does it not work with just NUMERIC?

OmarBenSalem

It does Sunny,; bur with qlik sense it appears as if it does not..sense does not even recognize it ! and that's why he thought it won't work :

see :

Capture.PNG

Sense won't even complete it :

Capture.PNG

sunny_talwar

I have seen that problem, but what I don't understand is how does NUMERICCOUNT works