Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

paolojolly
New Contributor III

Calculate deviation from the average value

Hi, I have a table like this


ITEMDAYUMVOL

AVERAGE 

DEVIATION FROM AVERAGE 

0050307/01/2013KG80308,4166667-228,4166667
0050308/01/2013KG110 -198,4166667
0050309/01/2013KG210,5 -97,91666667
0050310/01/2013KG80 -228,4166667
0050311/01/2013KG430 121,5833333
0050322/01/2013KG940 631,5833333
0050701/01/2013KG910420,4166667489,5833333
0050704/01/2013KG430 9,583333333
0050717/01/2013KG70 -350,4166667
0050718/01/2013KG362,5 -57,91666667
0050721/01/2013KG670 249,5833333
0050722/01/2013KG80 -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

1 Solution

Accepted Solutions

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

See attached qvw.


talk is cheap, supply exceeds demand
5 Replies

Re: Calculate deviation from the average value

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.


talk is cheap, supply exceeds demand
preminqlik
Valued Contributor II

Re: Calculate deviation from the average value

sum(aggr(

sum(

  VOL -

  (

       sum( TOTAL VOL) /

       (

            $(data_fin) - $(data_ini)

       )

  )

),

VOL,UM,DAY,ITEM))

paolojolly
New Contributor III

Re: Re: Calculate deviation from the average value

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

See attached qvw.


talk is cheap, supply exceeds demand
paolojolly
New Contributor III

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

many thanks for the help

Community Browser