topic AGGR vs SET ANALYSIS why do my numbers differ in QlikView

AGGR vs SET ANALYSIS why do my numbers differ

Tue, 29 Jun 2010 17:15:31 GMT

Hi,

I am investigating the use of the AGGR function, but the values returned dont seem to be what i expect

the following expression

count({<Month = {'May 2010'}, EmpStatus = {'Active'}>} [Employee NO])

returns 7895

I try and do the same with AGGR using

count(aggr(if(EmpStatus= 'Active', if(Month = ScorecardMonth, [Employee NO])),[Employee NO]))

But this only gives me 718?

7895 is the right answer, where i am i going wrong?

AGGR vs SET ANALYSIS why do my numbers differ

Tue, 29 Jun 2010 19:58:12 GMT

in the example above ScorecardMonth = 'May 2010'

AGGR vs SET ANALYSIS why do my numbers differ

sicilianif — Tue, 29 Jun 2010 20:09:34 GMT

I am probably missing something, but why are you trying to use the AGGR function> why wouldn't you just have:

count(if(EmpStatus= 'Active', if(Month = ScorecardMonth, [Employee NO])))

AGGR vs SET ANALYSIS why do my numbers differ

Tue, 29 Jun 2010 20:32:13 GMT

Thanks. You're not missing anything, i think it's me overcomplicating things! .... I've used your amendment above and it works a treat!

Any guidance on when you need to use the AGGR function and when not to?

AGGR vs SET ANALYSIS why do my numbers differ

johnw — Wed, 30 Jun 2010 00:20:11 GMT

You use the aggr() function when you basically need a temporary table to compute something. Like lets say you have a bunch of sales by customer. Let's say you want to know the most recent first sale to any customer. Let's say customer A bought something in January, February and March, customer B bought something in February, March and May, and customer C bought something in March, April and May. The earliest each customer bought anything is A=January, B=February, C=March. So you want to return March. March isn't the maximum month, and it's not the minimum month. It's a combination, and you ned to build a temporary table to get there. Aggr() is what lets you do that. In this case:

max(aggr(min(SalesMonth),Customer))