Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Running Total

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'.

DateKeyResidualResidualTotal
1/1/2014A2020
1/2/2014A2020
1/3/2014A2020
1/4/2014A2020
1/5/2014B90110
1/6/2014B90110
1/7/2014B90110

Any help would be greatly appreciated.

1 Solution

Accepted Solutions
rwunderlich
MVP & Luminary
MVP & Luminary

How about:

rangesum(above(TOTAL aggr(max(Residual),Key),0,9999))

-Rob

View solution in original post

6 Replies
preminqlik
Specialist II
Specialist II

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)asResidual

Resident RESIDUAL;

RESIDENT_TOTAL_STAGE2:

Load Key,

if(RowNo()=1,Residual,

Residual+rangesum(Peek('ResidualTotal'),0) )asResidualTotal

Resident RESIDENT_TOTAL_STAGE1

;

DROP Table RESIDENT_TOTAL_STAGE1;

Not applicable
Author

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

anbu1984
Master III
Master III

Dim: Date, Key

Expr:If(Aggr(RowNo(),Key)=1,Residual,If(Not IsNull(Aggr(RowNo(),Key)),Residual + Above(TOTAL Residual),Above(TOTAL Column(1))))

147075.png

anbu1984
Master III
Master III

Try this expr

If(Aggr(RowNo(),Key),Residual+ Alt(Above(TOTAL Residual),0),Above(TOTAL Column(1)))

rwunderlich
MVP & Luminary
MVP & Luminary

How about:

rangesum(above(TOTAL aggr(max(Residual),Key),0,9999))

-Rob

View solution in original post

iktrayanov
Creator III
Creator III

See attached.

Hope that helps.

Regards,

Ivan