Distinct Values with Aggr function and set analysis

[#1]

Hi, I'm trying to report on the number of jobs per week above our abandon rate. The data I have has several rows per job, so I need to calculate the sum of abandon / sum of calls, work out which are above the abandon rate target, and then count the distinct jobs that result.

I'll attach in a moment some sample data and a couple of the attempts that I had tried.

I would appreciate any help.

Thanks,

Richard

[#2 · Accepted solution]

You're close, but the aggr function is slightly off. This is the expression in your example.

if(sum(AGGR(sum(Abandon) / (sum(Calls)), JobNumber))>0.02,JobNumber)

The way this is written, you are taking the sum of the abandon rate for all jobs and then asking for a job number to be returned. What you are wanting to do is have the if challenged and answered before the aggr resolves like this:

count(AGGR(If(sum(Abandon) / sum(Calls) > .02, JobNumber), JobNumber))

In this, the if is being evaluated for each JobNumber and returning the JobNumber. Then a count function is used against the returned array of job numbers.

The result is a count of jobs exceeding abandon rate. Similarly, you could use concat instead of count and get a list of the jobs specifically.