Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
Highlighted
greend21
Contributor II

Calculating Average with Set Analysis

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!

1 Solution

Accepted Solutions

Re: Calculating Average based

May be this

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

10 Replies

Re: Calculating Average based

Is this not working?

Avg([#OfCalls])

or

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

mwoolf
Honored Contributor II

Re: Calculating Average based

Create a WorkDays flag in script. Then:

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

greend21
Contributor II

Re: Calculating Average based

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

greend21
Contributor II

Re: Calculating Average based

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

Re: Calculating Average based

May be this

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

greend21
Contributor II

Re: Calculating Average based

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

Re: Calculating Average based

Yes

mwoolf
Honored Contributor II

Re: Calculating Average based

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

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

greend21
Contributor II

Re: Calculating Average based

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