topic Re: Re: Re: Calculate deviation from the average value in QlikView

Calculate deviation from the average value

paolojolly — Fri, 20 Dec 2013 18:07:12 GMT

Hi, I have a table like this

ITEM	DAY	UM	VOL	AVERAGE	DEVIATION FROM AVERAGE
00503	07/01/2013	KG	80	308,4166667	-228,4166667
00503	08/01/2013	KG	110		-198,4166667
00503	09/01/2013	KG	210,5		-97,91666667
00503	10/01/2013	KG	80		-228,4166667
00503	11/01/2013	KG	430		121,5833333
00503	22/01/2013	KG	940		631,5833333
00507	01/01/2013	KG	910	420,4166667	489,5833333
00507	04/01/2013	KG	430		9,583333333
00507	17/01/2013	KG	70		-350,4166667
00507	18/01/2013	KG	362,5		-57,91666667
00507	21/01/2013	KG	670		249,5833333
00507	22/01/2013	KG	80		-340,4166667

and for each item I need to calculate in a pivot table the deviation from the average value.

In the pivot I use then expression

sum(

VOL -

(

sum( TOTAL VOL) /

(

$(data_fin) - $(data_ini)

)

and it works correctly if i select only one ITEM, but does not work if I select two item.

Can someone help me?

Thanks

Re: Calculate deviation from the average value

Gysbert_Wassenaar — Fri, 20 Dec 2013 18:20:03 GMT

I doubt that expression actually works at all. There's a nested sum without an aggr to go along with it. Nesting aggregation functions requires use of the aggr function.

What you can try is create a variable vTotal as =sum(total VOL) and use that variable in your expression:

sum(VOL - $(vTotal)/($(date_fin)-$(data_ini)))

If that doesn't help, please post an example document that demonstrates the problem.

Re: Calculate deviation from the average value

preminqlik — Fri, 20 Dec 2013 18:22:28 GMT

sum(aggr(

sum(

VOL -

(

sum( TOTAL VOL) /

(

$(data_fin) - $(data_ini)

)

VOL,UM,DAY,ITEM))

Re: Re: Calculate deviation from the average value

paolojolly — Fri, 20 Dec 2013 19:18:44 GMT

Thanks but the problem is not solved. I post an example document that demonstrates the problem. If i select only item 00503 the dev value is correct(21878,84), but if i select two items i have the problem(dev becomes -79812,54).

thank you very much for help

Re: Re: Re: Calculate deviation from the average value

Gysbert_Wassenaar — Sat, 21 Dec 2013 07:40:27 GMT

See attached qvw.

Re: Re: Re: Calculate deviation from the average value

paolojolly — Mon, 23 Dec 2013 07:27:46 GMT

many thanks for the help