Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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.
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?
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)
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.
why are we using TOTAL DISTINCT ???
can you please explain the expression
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 |