Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
in the example above ScorecardMonth = 'May 2010'
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])))
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?
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))