Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.