Distinct Values with Aggr function and set analysis

Thu, 03 Dec 2009 23:53:58 GMT

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

Fri, 04 Dec 2009 00:20:51 GMT

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.

topic Distinct Values with Aggr function and set analysis in QlikView

Distinct Values with Aggr function and set analysis

Distinct Values with Aggr function and set analysis