Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
paolojolly
Creator
Creator

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
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

See attached qvw.


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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
Specialist II
Specialist II

sum(aggr(

sum(

  VOL -

  (

       sum( TOTAL VOL) /

       (

            $(data_fin) - $(data_ini)

       )

  )

),

VOL,UM,DAY,ITEM))

paolojolly
Creator
Creator
Author

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
Partner - Champion III
Partner - Champion III

See attached qvw.


talk is cheap, supply exceeds demand
paolojolly
Creator
Creator
Author

many thanks for the help