cumulative sum with descending order

[paulwalker]

Hi Experts,

Hope everything fine!

I'm facing couple of issues on cumulative sum (default cumulative sum working fine from top to bottom, but my requirement is it should be descending order cumulative).

Ex: 

ID	Sales
QlikView	100
PBI	50
Tableau	150
Spotfire	120

Output would be:

ID	Sales	Cumulative Sum
QlikView	100	370
PBI	50	420
Tableau	150	150
Spotfire	120	270

Can you give a try please for me ?

Thanks in Advance!

[paulwalker]

@marcus_sommer , @Bastien_Laugiero , @john_wang , @Steve_Nguyen 

@MK_QSL , @rwunderlich,  @MayilVahanan , @MarcoWedel , @tresesco 

Can you please help me on this 

or

This is also similar one

https://community.qlik.com/t5/New-to-Qlik-Sense/Cumulative-sum/td-p/2048404

Thanks in Advance!

[marcus_sommer]

You will need at first an appropriate ordering of the dimension probably just the expression of: sum(Sales) with a descending order and then something like rangesum(above(sum(Sales), 0, rowno()))

[paulwalker]

Yes Marcus, but the problem is I'm doing two columns for cumulative.

Example: Sales 1 column is descending order and calculated Cumulative Sales 1 

at the same time have to calculate Cumulative Sales 2. 

ID	Site	Sales 1	Sales 2	Cumulative Sales1	Cumulative Sales 2
QlikView	Site1	100	100	100	370
PBI	Site2	45	50	145	420
Tableau	Site3	25	150	170	150
Spotfire	Site4	8	120	178	270

is there any possibility for two cumulative ? 

not sure if it is possibility or not - user's wants both in same chart 

[marcus_sommer]

Within a classical logic it's not available because there is only one ordering possible. Maybe you could connect a second ordering by an aggr() wrapping of the sum(Sales) and/or also around the rangesum() but I could imagine that's rather tricky and by larger data-sets not very performant.

Beside this I have some doubts about the readability and interpretation from this information by displaying it in the mentioned way. I think there are better ways to present this data.

[Or]

I'm with @marcus_sommer on this one, but it may be possible to work around the problem using Chart-level scripting, though I'm not sure how exactly one would go about doing that efficiently (a solution with o(n^2) should be possible at the very least).

[paulwalker]

Marcus, I couldn't find any solutions using aggr function - could you please suggest any solution 

ID	Site	Sales 1	Sales 2	Cumulative Sales1	Cumulative Sales 2	Sales 1 Total	Sales 2 Total	Sale 1 %	sales 2 %	Sales1 bucket	Sales 2 Bucket
QlikView	Site1	100	100	100	370	178	420	56.18%	88.10%	30%-60%	80%-100%
PBI	Site2	45	50	145	420	178	420	81.46%	100.00%	80%-100%	80%-100%
Tableau	Site3	25	150	170	150	178	420	95.51%	35.71%	80%-100%	30%-60%
Spotfire	Site4	8	120	178	270	178	420	100.00%	64.29%	80%-100%	60%-80%

my final output is 

ID	Site	Sales 1	Sales 2	Sales1 bucket	Sales 2 Bucket
QlikView	Site1	100	100	30%-60%	80%-100%
PBI	Site2	45	50	80%-100%	80%-100%
Tableau	Site3	25	150	80%-100%	30%-60%
Spotfire	Site4	8	120	80%-100%	60%-80%

[tresesco · Accepted solution]

Cummulative Sales 1 :

rangesum(above(total sum([Sales 1]), 0, rowno(total)))

Cummulative Sales 2 :

Aggr(rangesum(above(total sum([Sales 2]), 0, rowno(total))), (ID, (TEXT, DESCENDING)))

[paulwalker]

Thanks for reply @tresesco ,

I have tried this earlier - but not working for me (I'm using 12.7 version QlikView)

[paulwalker]

Working @tresesco  - Thank you 😊

Aggr(rangesum(above(total sum([Sales 2]), 0, rowno(total))), ([ID], (TEXT, DESCENDING)))

Just ID kept in square bracket - working fine.