topic Re: Average expression giving wrong result in QlikView

Average expression giving wrong result

Wed, 02 Jan 2013 15:22:20 GMT

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

Gysbert_Wassenaar — Wed, 02 Jan 2013 15:27:50 GMT

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

Wed, 02 Jan 2013 15:34:28 GMT

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

Wed, 02 Jan 2013 15:35:09 GMT

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

montero91 — Wed, 02 Jan 2013 15:38:57 GMT

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

Thu, 03 Jan 2013 09:20:22 GMT

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

Tue, 05 Nov 2013 12:10:10 GMT

why are we using TOTAL DISTINCT ???

can you please explain the expression

Re: Average expression giving wrong result

montero91 — Mon, 11 Nov 2013 22:13:04 GMT

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