Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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)))
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.
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 ',');
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.
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)))