topic aggr function with condition in QlikView

aggr function with condition

Wed, 11 Aug 2010 17:26:21 GMT

Hi,
I have a problem with aggr function when I have a condition in the function.

First I wrote like this:
sum(aggr(
sum(calls_ans)
,team,agent))

but it was not right, I need a condition so I wrote like this:
sum(aggr(
sum(if (agent=destination,calls_ans,0))
,team,agent))

This give me to large sum. It seems to be a problem to have a condition for "agent" when one of the dimension also are "agent".

Please help me understand how aggr works.

aggr function with condition

johnw — Wed, 11 Aug 2010 21:48:30 GMT

Aggr() works just like a straight table. So this should give you a result as if you had a table with dimensions team and agent, expression sum(if(agent=destination,calls_ans,0)), and a total row that was sum of rows. If you actually build that straight table, perhaps it will help show what's going on.

One thing that looks suspicious to me is the use of "destination". Is that a field? If it's a field, and more than one destination is selected, I'd expect the sum to return 0. But you say you have a large sum, so I suppose that's not it.

aggr function with condition

Thu, 12 Aug 2010 09:20:24 GMT

I have two tables, they look like this (there are more columnes but I only show the intresting one here):

table1
Key agent team status
1 Kalle teamA oncall
1 Kalle teamA wrapup
2 Pelle teamA oncall
2 Kalle teamA oncall
2 Kalle teamA wrapup

table2
Key destination calls_ans calls_ans_req
1 Kalle 1 0
2 Pelle 1 0
2 Kalle 0 1

This means that for call with Key=1 it is Kalle who has answered the call. For call with Key=2 it is Pelle who has answered the call and then he requeued the call and the next time it is Kalle who has answered the call.

What I was trying to do was to calculate how many calls each agent has taken. Thats why I tried with condition destination=agent but I don't get it to work.

aggr function with condition

jonathandienst — Thu, 12 Aug 2010 10:09:42 GMT

Perhaps what you need to do is create a common key field between the two tables. This is done by having a field with the same name in each table. In your case, "agent" would be the key. Use destination as agent in the load statement for table 2.

If you need destination as a field because it is used elsewhere, then you can also say something like:


LOAD Key,
 destination,
 destination AS agent,
...
FROM .....

Then your first aggr expression should work.

Or else just create a chart/table with dimension agent (or destination if you decide noot to follow the first suggestion) and expression sum(call_ans)

Hope these help

Jonathan