7 Replies Latest reply: Nov 11, 2013 5:13 PM by victor montero

# Average expression giving wrong result

Hi

I seem to be having problems with the average function it is giving me incorrect data and I can't figure out why

I want to average the sales revenue for each salesday, for all salesmen.

So my dimension is salesDay, and my expression avg(salesRevenue) and this is the table I get.

 Day1 Day2 Day3 Average Sales Revenue 1490 760 1210

The result above does not match the data which is shown in the following table

 Day1 Day2 Day3 John 1427 1865 2404 Andrew 2778 1881 3015 Matt null null null Jen 315 -2558 -2970

The averages should be-

day 1 - 1130

day 2 - 297

day 3 - 2449

I would be greatfull for any help.

Thanks

• ###### Re: Average expression giving wrong result

The average function disregards nulls, so it takes the sum of the three valid numbers and divides it by three. If you don't want that you could use sum(salesRevenue)/count(salesmen)

• ###### Re: Average expression giving wrong result

Thanks I considered this but it still doesn't explain the values qlikview gives. Excluding null values =

day 1-1506

day 2 -396

day 3- 816

I replaced my avg formula with sum(salesRevenue)/count(salesmen) to double check but its still giving me the same wrong avg calculation.

• ###### Re: Average expression giving wrong result

Thanks I considered this but it still doesn't explain the values qlikview gives. Excluding null values =

day 1-1506

day 2 -396

day 3- 816

I replaced my avg formula with sum(salesRevenue)/count(salesmen) to double check but its still giving me the same wrong avg calculation.

could the error be in the second table and the data it is displaying?

• ###### Re: Average expression giving wrong result

hi kt_sesor,

The null value does not exist, does not see Qlikview and is averaging the three values if you see.

Maybe, serve you an arithmetic.

Sum (SalesRevenue) / count (TOTAL DISTINCT salesmen)

• ###### Re: Average expression giving wrong result

why are we using TOTAL DISTINCT ???

can you please explain the expression

• ###### Re: Average expression giving wrong result

Thanks every on for your help, after breaking the issue down further I've found it was to do with my load script.

One sales man can have 2 different instances of sales revenue if they are from two different regions so it was averaged up by that. I think the best option for me is to separate the region from the main data load.

• ###### Re: Average expression giving wrong result

Hi Kerthi,

Ya que únicamente tenemos tres vendedores que tienen venta, Qlikview realiza un promedio de tres clientes. Suma y lo divide entre tres y tienes como resultado 1506.667.

Y lo que se espera es el promedio por lo cuatro vendedores.

Y como resultado del  count (TOTAL DISTINCT salesmen) te da como resultado 4 para todos los  vendedores.

Sin el Distinct corremos el riesgo de que un vendedor tenga más de una venta por lo que se repetiría el vendedor y el TOTAL es por la dimensión, ya quiero que el total de los vendedores en todos los renglones si pones count( distinct salesmen) el resultado en John seria 1.

Con esto tenemos la venta por vendedor entre el número total de vendedores teniendo como resultado Dia1 4520 / 4 vendedores y un promedio aritmético de 1130 que es al que se quiere llegar.

---------------------------------------------

Since we only have three salesmen who sell, QlikView makes an average of three customers. Sum and divide three to have resulted 1506,667.

And the average expected is at four salesmen.

As a result of the count (DISTINCT TOTAL salesmen) will result is 4 for all salesmen.

Without Distinct run the risk that a seller has more than one sale so it would repeat the seller and the "TOTAL" is the dimension, and I want the total salesman in all lines if you put count (distinct salesmen) , the John would result in 1.

With this we have for sale by seller between the total number of salesmen resulting Dia1 4520/4 salesman and an arithmetic average is 1130 which is i want.

 Día 1 TOTAL salesmen with Total Distinct Without TOTAL Without Distinct Arithmetic AVG. With TOTAL Distinct Juan 1427 4 1 2 356.75 Andrés 2778 4 1 4 694.5 Mate nulo 4 1 0 0 Jen 315 4 1 6 78.75 Total 4520 4 3 12 1130