Using Aggr function inside Set analysis

anuradhaa — Thu, 25 Sep 2014 10:14:16 GMT

Hi,

I'm Using below aggr function to calculate sums.

=Sum(Aggr(

money((if(OWNER='ABC',0,

(if(((OWNER='EFG' or OWNER='HIJ') and (Date#(MonthYear,'MMM-YY') <= date#('Jun-14','MMM-YY')))

,sum(EFG_Total),(if((OWNER='EFG'),sum(EFG_Total),(if((Date#(MonthYear,'MMM-YY') <= date#('Jun-14','MMM-YY')),

((if((sum(COUNT)<=3000),((sum(COUNT)*.1)*9.6),if((sum(COUNT)>3000 and sum(COUNT)<=4000),

((((sum(COUNT)-3000)*.083)+158)*9.6),((((sum(COUNT)-4000)*.071)+5897)*9.6))))) ,

((if((sum(COUNT)<=3000),((sum(COUNT)*.101)*9.6),if((sum(COUNT)>3000 and sum(COUNT)<=4000),

((((sum(COUNT)-3000)*.0897)+848)*9.6),

((((sum(COUNT)-4000)*.0487)+1646)*9.6)))))))))))))), OWNER,MonthYear))

Now i want to compare sums of two years (current year (selected year) and previous year. So i can use set analysis to compare it.

Sum({$<Year={$(=Max(Year)-1)}>}Value) and

Sum({$<Year={$(=Max(Year))}>}Value)

But i can't use Aggr here because if i select year it only display selected year since i use year inside my aggr.

Please help me

topic Using Aggr function inside Set analysis in QlikView