topic Unable to restrict Month in Aggr function in QlikView

Unable to restrict Month in Aggr function

raajgote — Tue, 05 Jul 2016 10:01:20 GMT

Hi All,

I have created below expression which will provide AVG of score using AGGR, I am trying to restrict the AGGR for initial 4 Months, but its not working,

= alt(num(Avg(Aggr((If((((sum({<MonthNum-={1,2,3,4},GlobalGroup = {'Software Services Operations'}>}[Direct Billable Utilization])/

sum({<MonthNum-={1,2,3,4},GlobalGroup = {'Software Services Operations'}>}[Standard Work Hours]))>=

(If(GetSelectedCount(Geomarket)>0 ,Sum(Distinct DBUGMTarget%SW)/GetSelectedCount(Geomarket),If(GetSelectedCount(Area)>0,Sum(Distinct DBUAreaTarget%SW)/GetSelectedCount(Area),0.68)))) and

((sum({<MonthNum-={1,2,3,4},MM_CompliantStatus={1},GlobalGroup = {'Software Services Operations'}>}TT_RegularHoursBooked)/

sum({<MonthNum-={1,2,3,4},Metric={'MM'},GlobalGroup = {'Software Services Operations'}>}StandardWorkDay))>=0.7) and

((sum({<MonthNum-={1,2,3,4},ME_CompliantStatus={1},GlobalGroup = {'Software Services Operations'}>}TT_RegularHoursBooked)/

sum({<MonthNum-={1,2,3,4},GlobalGroup = {'Software Services Operations'}>}StandardWorkDay))>=0.97)),

10, 0)),MonthNum)),'#,##0.0'),0)

when I select Month as 5,6 it shows correct result but when I select 1 to 6 Months, it shows wrong result, ideally it should show only 5,6 as we have restricted for 1 to 4 but unfortunately its doing AGGR on 6 months.

Any help on priority is highly appreciated!

BR,

Raaj...

Re: Unable to restrict Month in Aggr function

Anonymous — Tue, 05 Jul 2016 11:22:01 GMT

try to check individual expression where it is going wrong.

Re: Unable to restrict Month in Aggr function

sasiparupudi1 — Tue, 05 Jul 2016 11:31:25 GMT

I think you need a year dimension here..

alt(num(Avg(Aggr((If((((sum({<MonthNum-={1,2,3,4},Year={2016},GlobalGroup = {'Software Services Operations'}>}[Direct Billable Utilization])/

Re: Unable to restrict Month in Aggr function

raajgote — Tue, 05 Jul 2016 12:10:30 GMT

Thanks to all for quick response!

I have already checked individual expression and issue is there in below expression,

(If(GetSelectedCount(Geomarket)>0 ,Sum(Distinct DBUGMTarget%SW)/GetSelectedCount(Geomarket),If(GetSelectedCount(Area)>0,Sum(Distinct DBUAreaTarget%SW)/GetSelectedCount(Area),0.68))))

Instead of dynamic expression which is ultimately resulting into single value, when we add static value, it will give correct result.

BR,

Raaj...