cumulative sum in bar chart

[L_Hop]

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.

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.

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

Thanks.

OY

[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

while this:

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

returned:

[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.

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

[sunny_talwar]

makes sense ... my bad....