10 Replies Latest reply: Nov 23, 2016 6:13 AM by Sunny Talwar

# Rangesum cumulative with 2 dimensions

HI Qlikers,

I'm bulding a chart with cumulative sum and 2 dimensions. I found a lot of usefull informations on Qlik Community but I'm not completely finish with my task.

So I have a table with 3 dimensions. ID, Country, Month. So in my line chart I want to count my ID for each Country and do a cumulative sum for each month. So far I have this expression:

RangeSum(above(TOTAL Count(ID),0,RowNo(TOTAL)))

But my country are not treated individualy. How can I specify that I want only a rangesum for each country?

Thanks for your help

• ###### Re: Rangesum cumulative with 2 dimensions

Can you try this:

RangeSum(Above(Count(ID), 0, RowNo()))

• ###### Re: Rangesum cumulative with 2 dimensions

Hi,

With your expression (RangeSum(Above(Count(ID), 0, RowNo()))) I have a cumulative sum on Country.

With my expression (RangeSum(above(TOTAL Count(ID),0,RowNo(TOTAL)))) I have a cumulative sum on Country and Month.

... How can I have a cumulative sum only on Month? Each Country treated individualy in my chart ...

• ###### Re: Rangesum cumulative with 2 dimensions

Try this:

Aggr(RangeSum(Above(Count(ID), 0, RowNo())), Country, Month)

• ###### Re: Rangesum cumulative with 2 dimensions

Hi,

It worked nearly perfectly. The cumulative sum is taking each country individualy so that is great. I don't know why yet, but I will have the total amount on the first month and then it goes down until the end of the year. So I have to figure out how to make it reverse. Because usually you want to know the cumulative at the end of the year :-)

Thanks for your great help

• ###### Re: Rangesum cumulative with 2 dimensions

Since this is Qlik Sense, you can try this:

Aggr(RangeSum(Above(Count(ID), 0, RowNo())), Country, (Month, (NUMERIC, ASCENDING)))

• ###### Re: Rangesum cumulative with 2 dimensions

Function NUMERIC and ASCENDING do not exist in Qlik Sense ..

• ###### Re: Rangesum cumulative with 2 dimensions

Did you try if it works or not? Look here

The sortable Aggr function is finally here!

• ###### Re: Rangesum cumulative with 2 dimensions

Hi,

When I put the function NUMERIC for exemple it is not reconized as a function (not in blue). So I thaught it is only Qlik View. And nothing changed because my Month is in text.

But thanks for the link I found there is also TEXT function, still it doesn't work because based on the text all the month are mixed up. But there is also LOAD_ORDER. This one worked perfectly!!

Thanks again for your great help.

• ###### Re: Rangesum cumulative with 2 dimensions

Awesome, I am glad it worked, but I advice you to convert your text field into a dual field for Month. May be not now, but you will def. be benefit from converting it down the line

Why don’t my dates work?

Get the Dates Right

• ###### Re: Rangesum cumulative with 2 dimensions

Alternatively, you can try this:

Aggr(Count(TOTAL <Country> ID) - RangeSum(Above(Count(ID), 0, RowNo())), Country, Month)