topic Re: Running total in QlikView

Running total

rcorcoran — Tue, 17 Feb 2015 12:50:24 GMT

I have the two expressions below. the first one brings back 13 mth line chart showing each months figures. I want it to bring back a running total of them so that Jan shows Jan figure and Feb shows Jan and Feb added together etc.

The same is for the second expression this is for the same period last year but I want it to show the running total of the month for example if the month is June then it should show Jan to June added together for the previous year.

=Sum({$<[PallCare Specialist] ={2},RDOKey={1},ReferredYYYY, YearMonth={">=$(=AddMonths(Max({<[PallCare Specialist] ={2}>}YearMonth), -12))<=$(=Max({<[PallCare Specialist] ={2}>}YearMonth))"}>}ActualValue)

num((((Sum({$<[PallCare Specialist]={2},ReferredYYYY={$(#=only(ReferredYYYY)-1)},ReferredMM={$(#=Max({<[PallCare Specialist]={2}>}ReferredMM))}>}ActualValue)))), '##0'),

any help appreciated

Message was edited by: rhona CORCORAN sample attached test 2 and 3 have a running value for the months and I need the chart to show a running value also and in the table I need the SPLY to show the running total for the period last year

Re: Running total

Tue, 17 Feb 2015 13:03:25 GMT

Hi Rhona,

I do not know if I understand it,

but to represent the sum of January and next January + February, sets on the expression pane flags on Progressive extended.

Accumulation - > Full Accumulation

The same is true for the second expression but with setanalysis to the previous year

Re: Running total

sunny_talwar — Tue, 17 Feb 2015 14:02:01 GMT

Are you using YearMonth as the dimension? If you are then you will need to use RangeSum(Above/Below(...)) functions to achieve what you are trying to do here.

Best,

Re: Running total

giakoum — Tue, 17 Feb 2015 14:16:12 GMT

have you tried accumulation?

Re: Running total

rcorcoran — Tue, 17 Feb 2015 14:18:17 GMT

Thanks Sunindra could you show me where I would put it below as I cant seem to get it right.

Thanks

num((((Sum({$<[PallCare Specialist]={2},ReferredYYYY={$(#=only(ReferredYYYY)-1)},ReferredMM={$(#=Max({<[PallCare Specialist]={2}>}ReferredMM))}>}ActualValue)))), '##0'),

Rhona

Re: Running total

sunny_talwar — Tue, 17 Feb 2015 14:21:18 GMT

It is not as straightforward. It would be best if you can share a sample application with some data in it and I can work to get the expression working for you.

Best,

Re: Running total

rcorcoran — Tue, 17 Feb 2015 14:23:41 GMT

Ok thanks

Rhona