Calculate deviation from the average value

[paolojolly]

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

[Gysbert_Wassenaar]

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.

[preminqlik]

sum(aggr(

sum(

  VOL -

  (

       sum( TOTAL VOL) /

       (

            $(data_fin) - $(data_ini)

       )

  )

),

VOL,UM,DAY,ITEM))

[paolojolly]

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

[Gysbert_Wassenaar · Accepted solution]

See attached qvw.

[paolojolly]

many thanks for the help