Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone.
I'm trying in vain to produce a calculation on Sales data as per the following details:
The % performance calculation in the attached QlikView application is not accumulating the way I want it to. The application shows sample data with Sales values, Target values, a Performance calculation (Sales - Target) and a % Performance calculation (Sales/Target). I have 'Full Accumulation' selected on the first 3 calculations and 'No Accumulation' on the % Performance Calculation. This produces a Daily % Performance value as expected. However, what I want to do is produce an accumulated % value. For example, for day 3 the % Performance should be 180/187=96.25%. It is displaying as 80% (chart 1), which is the % Performance for day 3 with no accumulation. If I switch on 'Full Accumulation' for the % Performance it displays as 294.35% (chart 2).
Can someone advise me on what calculation I need to use to produce what I am looking for.
Thanks & regards,
Eamonn.
2 ways:
The easy way - Change the expression Total mode to Average instead of Expression default. This should give you a correct accumulation according to your expectations.
The hard way - Use inter-record functions to calculate the rolling average:
rangeavg(above((Sales/Target),0,Rowno()))
Hi Johannes,
Thanks for getting back to me.
I need to use 'the hardway' you mention above so that I can produce a value on each line, however, the calculation is not working as I need it to. It is calculating the average for the current line and the lines above but this is not what I need. What I need is Sales/Target as a % for each individual line, bearing in mind that the Sales and Target values are accumulated.
Any further thoughts?
Thanks,
Eamonn.