Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)))
@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!
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()))
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
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.
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).
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% |
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)))
Thanks for reply @tresesco ,
I have tried this earlier - but not working for me (I'm using 12.7 version QlikView)
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.