topic Re: Calculating Average based in QlikView

Calculating Average with Set Analysis

greend21 — Wed, 25 Nov 2020 16:16:04 GMT

I'm trying to find out the average calls per day but only for work days. In my data, the sum(#of Calls) is 0 on non working days so maybe I could calculate the data by excluding those days where the sum(#ofCalls)=0, so maybe a count of that. I was thinking this would be some sourt of sum(#ofcalls)/Count(pick(match())) but I am not completely sure. I now think this might need set analysis.

Any help is greatly appreciated!

Re: Calculating Average based

sunny_talwar — Wed, 15 Nov 2017 15:28:47 GMT

Is this not working?

Avg([#OfCalls])

Avg({<[#OfCalls] -= {'0'}>} [#OfCalls])

Re: Calculating Average based

m_woolf — Wed, 15 Nov 2017 15:30:09 GMT

Create a WorkDays flag in script. Then:

=avg({<WorkDays={1}>}[#ofCalls])

Re: Calculating Average based

greend21 — Wed, 15 Nov 2017 15:37:00 GMT

These do not work. It may be because I have daily data broken down by employees, although in this case I'm looking for the monthly average. Each day has multiple records. The actual average for Sept should be 194 but the following are the results:

Avg([#OfCalls]) = 40.6

Avg({<[#OfCalls] -= {'0'}>} [#OfCalls]) = 42.58

Re: Calculating Average based

greend21 — Wed, 15 Nov 2017 15:38:41 GMT

Do you mean have an if statement in the script that = Workdays? Could you explain this more?

Re: Calculating Average based

sunny_talwar — Wed, 15 Nov 2017 15:42:01 GMT

May be this

Sum([#OfCalls])/Count({<[#OfCalls] -= {'0'}>} DISTINCT Days)

Re: Calculating Average based

greend21 — Wed, 15 Nov 2017 15:50:22 GMT

This returned a null value. Where you have days it would actually be my date field, correct?

Re: Calculating Average based

sunny_talwar — Wed, 15 Nov 2017 16:03:46 GMT

Yes

Re: Calculating Average based

m_woolf — Wed, 15 Nov 2017 17:47:10 GMT

Is there some CallDate field? If so, in script something like:

if(weekday(CallDate)<5,1,0) as Workday,

Re: Calculating Average based

greend21 — Wed, 15 Nov 2017 17:57:51 GMT

I tried it again and it worked this time. Thanks!

Re: Calculating Average with Set Analysis

Anonymous — Wed, 15 Nov 2017 18:00:17 GMT

Maybe

Avg(if(weekday(DateField)<5,[#of Calls]))