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

    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

        • Re: Average expression giving wrong result
          Gysbert Wassenaar

          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
            victor montero

            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

              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
                victor montero

                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