1 Reply Latest reply: Dec 3, 2009 7:20 PM by Aaron Witt RSS

    Distinct Values with Aggr function and set analysis

    rhadl1

      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

        • Distinct Values with Aggr function and set analysis

          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.