Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a data set similar to the below table. For every 'Date' and 'Key' I have a 'Residual' value. This residual value will be the same for each key. However, I am having some issues with calculating the 'ResidualTotal' field. This field should be a running total of the residual values for each key. I know how to incorporate the rangesum/above function (i.e. rangesum(above(sum(residual),0,rowno()))) but I do not know how to implement it in this scenario when I do not want it by row number and I cannot replace rowno() with 'Date' and 'Key'.
Date | Key | Residual | ResidualTotal |
1/1/2014 | A | 20 | 20 |
1/2/2014 | A | 20 | 20 |
1/3/2014 | A | 20 | 20 |
1/4/2014 | A | 20 | 20 |
1/5/2014 | B | 90 | 110 |
1/6/2014 | B | 90 | 110 |
1/7/2014 | B | 90 | 110 |
Any help would be greatly appreciated.
hi there, hope this helps you , find attachment
RESIDUAL: |
LOAD * INLINE [
Date, Key, Residual
1/1/2014, A, 20
1/2/2014, A, 20
1/3/2014, A, 20
1/4/2014, A, 20
1/5/2014, B, 90
1/6/2014, B, 90
];
RESIDENT_TOTAL_STAGE1:
Load distinct Key,rangesum(Residual,0) | as | Residual |
Resident RESIDUAL;
RESIDENT_TOTAL_STAGE2:
Load Key,
if(RowNo()=1,Residual,
Residual+rangesum(Peek('ResidualTotal'),0) ) | as | ResidualTotal |
Resident RESIDENT_TOTAL_STAGE1
;
DROP Table RESIDENT_TOTAL_STAGE1;
Thank you, Prem. This method definitely works but would you know how to put this in an expression instead of the script? I eventually want to chart this and the running total will be dynamic depending on which keys the user selects.
Thanks
Dim: Date, Key
Expr:If(Aggr(RowNo(),Key)=1,Residual,If(Not IsNull(Aggr(RowNo(),Key)),Residual + Above(TOTAL Residual),Above(TOTAL Column(1))))
Try this expr
If(Aggr(RowNo(),Key),Residual+ Alt(Above(TOTAL Residual),0),Above(TOTAL Column(1)))
How about:
rangesum(above(TOTAL aggr(max(Residual),Key),0,9999))
-Rob
See attached.
Hope that helps.
Regards,
Ivan