Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

tleivamo
New Contributor III

How to get the correct value with average function?

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...

3 Replies

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

‌Try this:

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

tleivamo
New Contributor III

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

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.

Highlighted
tleivamo
New Contributor III

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

Hi,

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

In case not, please close this threat.

Thank you.