topic Re: cumulative sum with descending order in App Development

cumulative sum with descending order

paulwalker — Thu, 16 Mar 2023 09:05:49 GMT

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!

Re: cumulative sum with descending order

paulwalker — Thu, 16 Mar 2023 11:13:10 GMT

@marcus_sommer , @Bastien_Laugiero , @john_wang , @Steve_Nguyen

@MK_QSL , @rwunderlich, @MayilVahanan , @MarcoWedel , @tresB

Can you please help me on this

This is also similar one

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

Thanks in Advance!

Re: cumulative sum with descending order

marcus_sommer — Thu, 16 Mar 2023 12:01:51 GMT

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

Re: cumulative sum with descending order

paulwalker — Thu, 16 Mar 2023 12:14:19 GMT

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

Re: cumulative sum with descending order

marcus_sommer — Thu, 16 Mar 2023 12:46:59 GMT

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.

Re: cumulative sum with descending order

Or — Thu, 16 Mar 2023 12:51:15 GMT

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

Re: cumulative sum with descending order

paulwalker — Fri, 17 Mar 2023 03:45:35 GMT

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%

Re: cumulative sum with descending order

tresB — Fri, 17 Mar 2023 05:58:34 GMT

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

Re: cumulative sum with descending order

paulwalker — Fri, 17 Mar 2023 06:41:04 GMT

Thanks for reply @tresB ,

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

Re: cumulative sum with descending order

paulwalker — Fri, 17 Mar 2023 07:17:14 GMT

Working @tresB - Thank you 😊

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

Just ID kept in square bracket - working fine.

Re: cumulative sum with descending order

paulwalker — Fri, 17 Mar 2023 09:36:00 GMT

@tresB , any possibility on this

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

Re: cumulative sum with descending order

tresB — Fri, 17 Mar 2023 10:06:43 GMT

Yes, one way is using labels, like:

If([Group Total]<>Above([Group Total]), RangeSum(Above(Total Cummulative), [Group Total]),Above(Total Cummulative))

I posted there in the orig. post as well.

Note: Please like and mark correct answers to close the thread; that also helps others looking for solutions to similar issues.

Re: cumulative sum with descending order

tresB — Mon, 20 Mar 2023 05:37:26 GMT

Add 'nodistinct' in aggr(), like: Aggr( Nodistinct ...

Re: cumulative sum with descending order

tresB — Mon, 20 Mar 2023 06:30:06 GMT

Your sort order is Site_Sales, you have to correct that; use sum(Site_Sales) and play a bit to adjust to handle the cases of conflicts