Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jindrichk
Contributor II
Contributor II

Straight table - sum of rows with higher ID

Hi,

I'm having a simple table with columns ID and Value. I'm trying to find Expression for a straight table which for each line it will sum values from all others rows which have ID greater than ID of the current line.

So like

ID    Value    LookingForThis

5          10              -

4          5              10 (From ID = 5)

3          7              15 (From ID = 5 and 4)

2          6              22 (ID 5, 4 and 3)

1          5              28 (ID , 4, 3 and 2)



I hope it is clear. The real example is a bit more complicated but this is the core I believe.


I was thinking about Set Analysis like this

sum({<ID = {">CurrentID"}>} Value)


but the issue is that I don't know how to get the CurrentID in SA.


Thanks for all reactions.



Regards,

Jindra

1 Solution

Accepted Solutions
sunny_talwar

With 11.2 you are left with only 3 options

1) Sort ID in descending order in the script and use this

Aggr(RangeSum(Above(Sum(Value), 1, RowNo())), ID)

2) Do nothing in the script, but sort your chart correctly and then use this

RangeSum(Above(Sum(Value), 1, RowNo()))

3) Use The As-Of Table

View solution in original post

8 Replies
m_woolf
Master II
Master II

If your table is sorted as shown, you can use the above() function.

jindrichk
Contributor II
Contributor II
Author

Tank you m w.

Unfortunately, it is usually not. There are other fields used for sorting.

sunny_talwar

May be this

RangeSum(Above(Sum(Value), 1, RowNo()))

jindrichk
Contributor II
Contributor II
Author

The problem is that it does not have to be always sorted like this. The higher ID rows can be also below the current line.

sunny_talwar

Do you have QV12 or above? If you do, then you can do this

Aggr(RangeSum(Above(Sum(Value), 1, RowNo())), (ID, (NUMERIC, desc)))

jindrichk
Contributor II
Contributor II
Author

Unfortunately on version 11.20.

sunny_talwar

With 11.2 you are left with only 3 options

1) Sort ID in descending order in the script and use this

Aggr(RangeSum(Above(Sum(Value), 1, RowNo())), ID)

2) Do nothing in the script, but sort your chart correctly and then use this

RangeSum(Above(Sum(Value), 1, RowNo()))

3) Use The As-Of Table

jindrichk
Contributor II
Contributor II
Author

Thank you, I'm going with doing it in a script. It will restrict some possible selections for a user but will give me correct results.