topic Re: Aggr function problem in QlikView

Aggr function problem

Sat, 22 Oct 2016 08:56:53 GMT

Hi All,

I am having a problem with the Aggr function and i hope someone can help me.

Here is what I am trying to achieve:

I need to find the amount of agents that achieve more less than 6 calls per day.

This is the formula I am using without luck:

count(distinct aggr(if(sum(Inbound)+sum(Outbound)<6,Agent,Dates),Agent&Dates)).

and this is a sampel of my data:

Agent	Date	Inbound	Outbound
1	01/01/2001	4	9
2	01/02/2001	4	1
2	01/02/2001	1	2
3	01/01/2011	3	0
3	01/0122011	4	1

Agent Date inbound outbound

1 01/01/2001 4 6

2 01/02/2011 3 3

2 01/02/2011 5 4

3 01/01/2001 7 7

3 01/02/2011 5 2

I also tried to use set analysis but nothing.

Is there anyone that can help me?

Re: Aggr function problem

marcus_sommer — Sat, 22 Oct 2016 13:38:49 GMT

Try this:

sum(aggr(if(sum(Inbound)+sum(Outbound)<6, 1, 0), Agent,Dates))

- Marcus

Re: Aggr function problem

Sat, 22 Oct 2016 13:51:53 GMT

Hi Marcus, thanks for your answer.

Your code seems to work a lot better but it gives me the wrong result ( I was expecting a result of 195 and it gives me 196 ).

Can you please explain me how your approach works?

Re: Aggr function problem

sunny_talwar — Sat, 22 Oct 2016 14:01:15 GMT

May be try this:

Count(DISTINCT {<Agent = {"=Sum(Inbound) + Sum(Outbound) < 6"}>} Agent)

Re: Aggr function problem

Sat, 22 Oct 2016 14:08:56 GMT

Hi Sunny

Thanks for your help.

I believe that your solution will take in consideration just the Agents, is this correct? I would need to check the values for the combination between Agent and Dates

Re: Aggr function problem

marcus_sommer — Mon, 24 Oct 2016 05:05:02 GMT

My suggested approach with the aggr-function will create a (virtual) table with Agent and Dates as dimension and a result-column of the condition within the if-loop - if(condition, true, false) - which will be then simply aggregated through the outer sum-function.

To find the difference between the expression and your expected result create a tablebox of Agent, Dates, Inbound and Outbound and a unique record-identifier - if no one exists you could create one within the script with rowno() - and then checking your data directly. There might be a duplicate record or another issue with the data-quality.

- Marcus

Re: Aggr function problem

sunny_talwar — Mon, 24 Oct 2016 05:43:56 GMT

I am not entirely sure what you are looking to do, but have you tried this expression? Is it not working the way you would expect?