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

does this work when u select a month?

sunny_talwar

It probably won't.... unless you supply set analysis to ignore Month

OmarBenSalem

Yes:

sthing like this: (if u want to keep only the month before the selected one and the limit to be the selected month

sum({<Month={"=$(=max(Month))"}>}

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


or

like this: if he wants to keep every month

sum({<Month>}

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

sunny_talwar

Don't think either of them are right... you missed to ignore selection in inner aggregation

Sum({<Month>} Sales)

sunny_talwar

In addition, what is the point of doing this?

sum({<Month={"=$(=max(Month))"}>} Aggr(RangeSum(Above(Sum({<Month>}Sales), 0, RowNo())),Year, (Month, (NUMERIC),ASCENDING))))

For a single selection in Month field, the above will be equal to this

Aggr(RangeSum(Above(Sum({<Month>}Sales), 0, RowNo())),Year, (Month, (NUMERIC),ASCENDING)))

OmarBenSalem

It's Month={"<=$(=max(Month))"}

I tried this;

aggr(rangesum(above(Sum({<Product,Month={"<=$(=max(Month))"}>}Sales),0,RowNo())),Product,(Month,(numeric,ascending)))

result

Capture.PNG

while this:

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

returned:

Capture.PNG

sunny_talwar

You changed the expression... of course that would work now

sunny_talwar

In short, there are many different things you can do... but it sort of depends on what the user is looking to get. Do they want all the Months until selected or do they want to just see 1 month. I try to leave that for the users to decide and if they need help, we are all here to help

OmarBenSalem

Yes, my bad; I was going to copy this expression (the first time / I wrote it an hour ago lol) ; and realized I copied another one when u said that it won't work.

I don't mean u were wrong; I was just letting u know why I was going with thoses 'modified' expressions.

Capture.PNG

ps: the one who asked the question already've said that he want to show the cumulative sum till the selected month:

Capture.PNG

sunny_talwar

makes sense ... my bad....