Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Running Total

How about:

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

-Rob

6 Replies
preminqlik
Valued Contributor II

Re: Running Total

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

Re: Running Total

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
Honored Contributor III

Re: Running Total

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
Honored Contributor III

Re: Running Total

Try this expr

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

Re: Running Total

How about:

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

-Rob

iktrayanov
Contributor III

Re: Running Total

See attached.

Hope that helps.

Regards,

Ivan

Community Browser