asking about Average

[#1]

hi all

I wanna ask something about average in QV coz I have trouble about it.

I have a pattern to get UA value. the patters like this

UA = (P) / (O + P)

I have 5 UA value in decimal number:

UA1 = 80/744 = 10,75

UA2 = 1/384 = 0,26

UA3 = 15/744 = 2,01

UA4 = 50/744 = 6,72

UA5 = 62/744 = 8,33

If I calculate it with manual condition it will be like this (10,75 + 0,26 + 2,01 + 6,72 + 8,33)/5 = 28,07/5 = 5,61

but when I try with Qlikview I get different avg value. the value is 6,91.

when I trace it with this expression:

          =avg(aggr(((sum(ProductiveRelated))/(sum(OperatRelated) + sum(ProductiveRelated)))*100,EquipType))

I will get the value is 6.91.

the value 6.91 comes from different patter in qlikview. the pattern that I get is ((80+1+15+50+62)/5)/((744+384+744+744+744)/5) = 6,91

what I need to know is how to get value like manual condition in qlikview, Can you help me guys??

note: P and O value are different table.

thanks before

ardo

[tresesco]

Could you upload your sample qvw that shows the issue?

[#3]

my qvw data connect with database mysql and I get difficult to explain it

but with a simple explenation  the calculation is like this

normal pattern is :

UA = (P) / (O + P)

so if I try to find the avg of UA  then the pattern to get avg is :

avg UA = sigma((P)/(O+P))/count UA.

my difficulty is how to put it on qlikview

thank u

[tresesco]

Try to create a sample qvw and ipload here explaining the expected output. Otherwise, I guess your :

sigma((P)/(O+P))/count UA, could be something like: =Sum(P/(O+P))/count (Distinct UA)

Note: 'Distinct' might/might not be required based on your logic.

[jonathandienst]

Hi

The difference is because QV is calculating the ratio of the sums, which is not the same as the sum of the ratios (which is what you require).

if you are doing this in a straight table, select Sum of Rows in the Total Mode (Properties | Expression). If you are using a pivot table, then use an aggr() expression like this:

= Sum(Aggr(Sum(P) / (Sum(O) + Sum(P)), <your chart dimensions>))

Where <your chart dimensions> should be a comma separated list of the dimensions in your chart.

HTH

Jonathan

[hic]

I think you are complicating it...

If you make a chart with

     Dimension: EquipType (your UA1..UA5)

     Expression: Avg(ProductiveRelated/(OperatRelated+ProductiveRelated))/Count(distinct EquipType)

then I think you will get both the individual averages and the total right.

HIC

[lironbaram]

have a look at the attach example

[#8]

Hi All

thanks for all of your input to me. jonathan dients, thanks for your ideas...I ve explore it n I found solution for my

trouble...this is what I get

=(sum(aggr(sum(ProductiveRelated),Plant_No)/(aggr(sum(OperatRelated),Plant_No) + aggr(sum(ProductiveRelated),Plant_No))*100)/count(DISTINCT Plant_No))

ProductiveRelated,OperatRelated they are consist of 2 table with the same plant_No. so I use this solution

I Hope this pattern can be useful for us in future

thanks

ardo