Announcements
Sept. 27, 10AM ET Do more with Qlik, Insight Advisor – our intelligent AI-assistant in Qlik Sense: Register
cancel
Showing results for
Did you mean:
Creator III

## 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
MVP

May be this

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

10 Replies
MVP

Is this not working?

Avg([#OfCalls])

or

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

Master II

Create a WorkDays flag in script. Then:

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

Creator III
Author

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

Creator III
Author

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

MVP

May be this

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

Creator III
Author

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

MVP

Yes

Master II

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

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

Creator III
Author

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

Community Browser