topic Re: YTD expression for graph in QlikView

YTD expression for graph

Fri, 24 Aug 2012 10:20:00 GMT

Hi,

I would like to show on a combo graph :

my sales actuals and budget per month in column

my achievement to target YTD on a line.

I managed to do the achievement on a monthly basis with :

= sum(Actuals12)/sum(Sales2012Plan)

but this display achievement for each month only, not a cummulative YTD

how do I show for each month the YTD cummulative achievement

for month 2 for example :

(Month 1 actuals + month 2 Actuals) / (month 1 plan+ month 2 plan)-1

Month 7 would be :

(Month 1 actuals + ....+ Month 7 actuals)/(Month 1 plan +...+ Month 7 Plan)-1

Currently if I use the Full accumulation option with the above formula it display beyond the last actual month...

Thanks

Re: YTD expression for graph

Fri, 24 Aug 2012 10:22:32 GMT

On the expressions tab on your chart there is a box middle left you can tick full accum

Re: YTD expression for graph

Fri, 24 Aug 2012 10:28:40 GMT

that is the box I was refering to. it shows data beyond the last actuals data. I would need to be able to limit the accumulation to the last actual data point

Re: YTD expression for graph

Fri, 24 Aug 2012 10:31:45 GMT

also it cummulates the % for each month, not recalcute achievement to target for the sum of the months that are actuals :

it does :

sum(sum(actuals)/sum(plan))

instead of

(sum(actuals1) + sum(actuals 2)+...)/((sum(plan 1)+sum(plan 2)+...)

Re: YTD expression for graph

Tue, 28 Aug 2012 04:03:31 GMT

I found the answer via an other forum on another related topic :

if(aggr(sum(Actuals12),Month)>0,

Rangesum(above(Sum (Actuals12),0,rowno()))/Rangesum(above(sum(Sales2012Plan),0,rowno())))

But while it works perfectly, I don t understand the concept of Rangesum(above(

Can someone explain it to me ?

Thanks