Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Distinct Values with Aggr function and set analysis

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

Tags (1)
1 Solution

Accepted Solutions
Not applicable

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.

1 Reply
Not applicable

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.

Community Browser