topic Re: How to calculate a cummulative % in QlikView

How to calculate a cummulative %

Tue, 13 Nov 2012 21:23:26 GMT

Hi I'd like to calculate a cummulative % as shown by the value and the formula in the two right columns below -

Month	Appointments Made	No of Jobs
1	10	12	83.33%	=10/12	83.33%	=10/12
2	20	22	90.91%	=20/22	88.24%	=(10+20)/(12+22)
3	50	70	71.43%	=50/70	89.89%	=(10+20+50)/(12+22+70)

The first % column is fine; it's just the Appointments Made/No of Jobs. However the second % column, which is like the rolling average % is difficult to get in QlikView. I can't use the Full Accumulation tickbox; that doesn't seem to work, although it will accumulate the Appointments Made and the No of Jobs inidivdually, just not when they are a %.

Any suggestions would be great. Thanks in advance

Charlotte

Re: How to calculate a cummulative %

swuehl — Tue, 13 Nov 2012 23:12:33 GMT

Charlotte,

try something like

=rangesum(above(sum([Appointments Made]),0,rowno() )) / rangesum( above(sum([No of Jobs]),0,rowno() ))

Hope this helps,

Stefan

Re: How to calculate a cummulative %

Wed, 14 Nov 2012 15:33:45 GMT

Thanks very much. it's not giving me exactly the answer I want on row 3 but going to investigate and will let you know. thx again

Re: How to calculate a cummulative %

Thu, 15 Nov 2012 08:51:34 GMT

Thanks very much, my mistake, your answer was absolutely spot on! I just needed a bit of spare time to look at it and work out how it works. Thx again, much appreciated, Charlotte