29 Replies Latest reply: Feb 1, 2018 10:46 AM by Sunny Talwar

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

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

• ###### Re: cumulative sum in bar chart

try:

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

Result (per analogy):

aggr(rangesum(above(Sum(Sales),0,RowNo())),nom,num)

• ###### Re: cumulative sum in bar chart

Hello Omar,

provided script not working, result of the script is first picure, however month figures should  increase incremant like second picture. related month figures should be cumulatively summed.

• ###### Re: cumulative sum in bar chart

you want to eliminate the second dimension when u cumulate ur measure?

Because, the way things are with my expressions; each month; the second dimension will be cumulated..

see a and b :

if u want to cumulate the whole thing by month and sum up all ur values of ur second dim then do as follow:

aggr(rangesum(above(Sum(Sales),0,RowNo())),Month)

result:

• ###### Re: cumulative sum in bar chart

I want to cumulate the whole thing, if month Oct is selected, result should be like below picture.

aggr(rangesum(above(Sum(Sales),0,RowNo())),Month) is not working, result is the second pic.

• ###### Re: cumulative sum in bar chart

can u please share a test application so I can take a look at this?

Because it works for me (without selections..)

stalwar1, how can this work with selections?

because when I select a month, qlik will this only this month and thus would not be able the above months..

Any idea?

• ###### Re: cumulative sum in bar chart

and please try to also refer to these :

Accumulations

The As-Of Table

• ###### Re: cumulative sum in bar chart

This

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

• ###### Re: cumulative sum in bar chart

or this

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

• ###### Re: cumulative sum in bar chart

I think Omar is right, have you tried this?

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

• ###### Re: cumulative sum in bar chart

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)

• ###### Re: cumulative sum in bar chart

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

• ###### Re: cumulative sum in bar chart

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

• ###### Re: cumulative sum in bar chart

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))

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

• ###### Re: cumulative sum in bar chart

Give this a shot

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

• ###### Re: cumulative sum in bar chart

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

• ###### Re: cumulative sum in bar chart

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

• ###### Re: cumulative sum in bar chart

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 :

Sense won't even complete it :

• ###### Re: cumulative sum in bar chart

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

• ###### Re: cumulative sum in bar chart

does this work when u select a month?

• ###### Re: cumulative sum in bar chart

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

• ###### Re: cumulative sum in bar chart

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)))

• ###### Re: cumulative sum in bar chart

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

Sum({<Month>} Sales)

• ###### Re: cumulative sum in bar chart

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)))

• ###### Re: cumulative sum in bar chart

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:

• ###### Re: cumulative sum in bar chart

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

• ###### Re: cumulative sum in bar chart

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:

• ###### Re: cumulative sum in bar chart

makes sense ... my bad....

• ###### Re: cumulative sum in bar chart

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

• ###### Re: cumulative sum in bar chart

I think this should work :

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

Result:

and when we select a Month: