Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
eri
Contributor III
Contributor III

How to calculate cumulative sum without impacting sorting order?

Hi,  I have a table, with dimension = Scrap Code, measure= Qty(number of scrap code) I want to calculate accum coloumn & order table with Qty descending so as to get the top  scarp codes, and cumulative sum should not impact the order. Expected output- 

Scrap CodeQtyAccum
4P88
4W715
4H520
1I424
1B226

 

Please help

Labels (1)
2 Solutions

Accepted Solutions
JordyWegman
Partner - Master
Partner - Master

Hi Guddu,

Try this:

RangeSum(Above(TOTAL Sum(Qty), 0, RowNo(TOTAL)))

Jordy

Climber 

Work smarter, not harder

View solution in original post

eri
Contributor III
Contributor III
Author

Thanks Jordy...
Using your formula and one addition thing I discovered just now it worked for me.

So, if using range & above() function , sorting needs to be done by expression in the sorting tab under dimensions name.
This made it work 🙂

 

Many thanks for your help

View solution in original post

7 Replies
JordyWegman
Partner - Master
Partner - Master

Hi Guddu,

Try this:

RangeSum(Above(TOTAL Sum(Qty), 0, RowNo(TOTAL)))

Jordy

Climber 

Work smarter, not harder
eri
Contributor III
Contributor III
Author

I tried these but when I am using rangesum()/above() function by Qty Column is no longer sorted DESC.
I want to keep Qty column DESC always 

JordyWegman
Partner - Master
Partner - Master

That shouldn't be a problem. If you go to settings -> Sorting. Is Qty then at top? If not, put this on top.

Jordy

Climber

Work smarter, not harder
eri
Contributor III
Contributor III
Author

Tried that already, didn't work as above funtion doesn't work with built in sorting.
Any other options please?

JordyWegman
Partner - Master
Partner - Master

I don't know why it shouldn't work, because if I use your example, it works: 

2019-11-19 16_55_42-Community - jordy.wegman@climber.nl - Outlook.png

Jordy

Climber

Work smarter, not harder
eri
Contributor III
Contributor III
Author

Thanks Jordy...
Using your formula and one addition thing I discovered just now it worked for me.

So, if using range & above() function , sorting needs to be done by expression in the sorting tab under dimensions name.
This made it work 🙂

 

Many thanks for your help

david_hg96
Partner - Contributor III
Partner - Contributor III

Hi Jordy,

Thanks for your help,

it works but in my case using weeks, some of them has no data, so the cumulative gives me nulls:

david_hg96_0-1667321874564.png

how could I fix it?

Regards,

David HG