Qlik Community

Ask a Question

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

Day1Day2Day3
Average Sales Revenue14907601210

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

Day1Day2Day3
John142718652404
Andrew277818813015
Mattnullnullnull
Jen315-2558-2970

The averages should be-

day 1 - 1130

day 2 - 297

day 3 - 2449

I would be greatfull for any help.

Thanks

7 Replies
MVP & Luminary
MVP & Luminary

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)


talk is cheap, supply exceeds demand
Not applicable

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.

Not applicable

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?

Creator
Creator

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)

Not applicable

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.

Not applicable

why are we using TOTAL DISTINCT ???

can you please explain the expression

Creator
Creator

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 1TOTAL salesmen with Total DistinctWithout TOTALWithout DistinctArithmetic AVG. With TOTAL Distinct
Juan1427412356.75
Andrés2778414694.5
Matenulo4100
Jen31541678.75
Total452043121130