3 Replies Latest reply: Jan 17, 2018 10:23 PM by TOBIAS LEIVA MORENO RSS

    How to get the correct value with average function?

    TOBIAS LEIVA MORENO

      Hi everbody,

      I have a problem with function Avg(), due to I can´t get the real value when I try to use it.

      I have a table in excel like this:

       

      table.JPG

       

      I need to calculate the average time for each vacancy and display it for each Analist. In excel, I can get the table with these data (I use formule of course) and obtain the exactly values, but when I try to do in Qlik, the average isn't correct.

       

      I obtain the time response like this: DATE FINISH - DATE REQ. If I want to get the time average in Qlik,  I just select the pivot table with this data:

       

      Dimension: Year, Vacancy

      Measure: Avg([TIME RESPONSE])

       

      My pivot table looks like this:

      table1.jpg

      Here's the problem...the results in Qlik are very different when I use Average function

       

      table2.JPG

       

      So, I don't know how obtain the same results in both Qlik and Excel.

      Who can help me?!!

       

      P.D. Sorry if my english have some mistakes...

        • Re: How to get the correct value with average function?
          Sunny Talwar

          Try this:

           

          Avg(Aggr(Avg([TIME RESPONSE]), Year, Vacancy, Analist))

            • Re: How to get the correct value with average function?
              TOBIAS LEIVA MORENO

              Hi, that expression works for my example...but how could apply that concept in my excel file (attach) -it's in spanish but the idea is the same-. I want to provide this example:

               

              Vacancy

              Time

              Response

              MonthAnalist
              Seller30JanCarl
              Seller52JanCarl
              Seller15JanCarl
              Seller40JanFred
              Seller22JanFred
              Seller12FebCarl
              Seller10FebFred
              Seller30FebFred

               

              Average time response for January / Seller / Carl: 32,33

              Average time response for January / Seller / Fred: 31,00

              Average time response for January / Seller : average(32,33;31) -->31,66

               

              Average time response for Febrary/ Seller / Carl: 12

              Average time response for Febrary/ Seller / Fred: 20

              Average time response for Febrary / Seller : average(12;20) -->16


              If I want to create an average for Jan-Feb in a excel, the table looks like this

                  

              CarlFredTime Response Average
              Seller =average(Jan;Feb) =average(Jan;Feb) =average(Carl_Seller; Fred_Seller)


              CarlFredTime Response Average
              Seller 22,16 25,5 23,83

               

              So, when I try to reply this calculation in Qlik Sense (I use your expression), the result it's different to my file (attach)

               

              table4.jpg

              I need to do this for compare 2 years (2016-2017) per Analist per Vacancy (like pivot table). Maybe I have been doing something wrong, but I can't found it.

               

              I hope that my question can be solution.

              Thanks.

            • Re: How to get the correct value with average function?
              TOBIAS LEIVA MORENO

              Hi,

              about my problem or question, is it possible to do it?

              In case not, please close this threat.

               

              Thank you.