RangeSum with Substraction QlikSense

[eddykwan_]

Hi Everyone,

How to create logic expression for substraction in pivot table?

Here's my expectation :

1. I want to calculate substraction from Qty, so in column Calc1 value 50 is from 60 - 10 = 50

2. And value 40 is from 60 + 10 - 30 = 40

And These Calculation are sequential.

Please help to solve it.

Thank you

[hic · Accepted solution]

If the logic is "Sum of all above rows minus current row" then

RangeSum(Above( Sum(Qty),1,RowNo()),-Sum(Qty))

will do the trick. So, row 2 and all following rows are correctly calculated. But your first row doesn't follow this logic...

But with a combination of RangeSum() and Above() you can create almost any logic - it is just a matter of defining what you want to calculate. 

[brunobertels]

Hi 

Here my test in script 

Table:
load*,
rowno() as LoadID,

if(peek(ID,-1)<>ID,Qty,
if(isnull(peek(ID,-2)),peek(Qty,-1)-Qty,
peek(Qty,-2)+peek(Qty,-1)-Qty ))as Test1

;
LOAD * INLINE
[
ID,Qty
1,60
1,10
1,30
1,20
2,70
2,20
2,20
2,40

](delimiter is ',');

[eddykwan_]

Thanks for your answer Henric,

Maybe if you know how to make the first row to positive value, Please help me to give the expression.

[hic · Accepted solution]

It depends on what the logic is. If it is as simple as
"If row=1 then Current row, else Sum of all above rows minus current row"

then it's just
If(RowNo()=1,Sum(Qty),RangeSum(Above( Sum(Qty),1,RowNo()),-Sum(Qty)))