topic Re: Use Aggr inside set analysis issue in QlikView

Use Aggr inside set analysis issue

anuradhaa — Thu, 25 Sep 2014 12:40:19 GMT

Hi,

I use aggr function to calculate total values based on complex logic. So now i have correct totals. In my aggr function i use date.

Now i want to compare total of two years (selected year, selected year -1 ).

So i need to use set analysis. But the problem is when i use set analysis and aggr together it shows correct values with out year selection.

when i select a year it only shows the values for selected year.

Please help me to fix this.

Thank You

sushil353 — Thu, 25 Sep 2014 12:42:24 GMT

Hi,

Please share your expression.

~Sushil

crusader_ — Thu, 25 Sep 2014 12:44:21 GMT

Hi,

Please share your expression.

It's quite difficult to help without it.

Thanks.

Andrei

anuradhaa — Thu, 25 Sep 2014 12:47:06 GMT

I want to use this inside set analysis,

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

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

I want to use below agrr for Value

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))

anuradhaa — Thu, 25 Sep 2014 12:57:52 GMT

Hi I have attached my expression, please help me to solve this issue

anuradhaa — Thu, 25 Sep 2014 12:58:01 GMT

Hi I have attached my expression, please help me to solve this issue

Thu, 25 Sep 2014 16:56:04 GMT

you try using P() function inside set analysis and also search for nested set analysis on google.

Thanks,

Singh