topic Re: asking about Average in QlikView

asking about Average

Fri, 09 May 2014 04:31:08 GMT

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

Re: asking about Average

tresB — Fri, 09 May 2014 04:45:20 GMT

Could you upload your sample qvw that shows the issue?

Re: asking about Average

Mon, 19 May 2014 04:55:34 GMT

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

Re: asking about Average

tresB — Mon, 19 May 2014 05:08:36 GMT

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.

Re: asking about Average

jonathandienst — Mon, 19 May 2014 06:08:41 GMT

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

Re: asking about Average

hic — Mon, 19 May 2014 06:12:24 GMT

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

Re: asking about Average

lironbaram — Mon, 19 May 2014 06:53:52 GMT

have a look at the attach example

Re: asking about Average

Mon, 19 May 2014 10:42:04 GMT

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