Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Live Chat, June 29th 10AM ET. Bring your Qik Sense Enterprise, Client Managed questions! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
reivax31
Partner
Partner

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

1 Solution

Accepted Solutions
sunny_talwar

Try this:

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

View solution in original post

10 Replies
sunny_talwar

Can you try this:

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

reivax31
Partner
Partner
Author

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

sunny_talwar

Try this:

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

View solution in original post

reivax31
Partner
Partner
Author

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

sunny_talwar

Since this is Qlik Sense, you can try this:

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

reivax31
Partner
Partner
Author

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

sunny_talwar

Did you try if it works or not? Look here

The sortable Aggr function is finally here!

sunny_talwar

Alternatively, you can try this:

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

reivax31
Partner
Partner
Author

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.