Issues with Aggr function

Anonymous — Mon, 16 May 2016 08:50:30 GMT

I need to calculate the resource count for a particular month where the effort (HCM) of that resource is spread across projects. When the total effort of the resource is <=0.7. This gets categorized under limit and if the effort of the resource across projects is >0.7 then it is categorized as Above limit. Second aspect to consider is the Plantype.

As long as the project month is prior to current month we need to consider Actuals as Plantype and Allocations if its current month and beyond while considering the effort (HCM)

I have grouped efffort(HCM) using Resource id across projects to calculate the sum of HCM.

Issue that I am facing is when I select only May 2016 I get the correct values as shown in the attachment May 2016. But when i select multiple months these values are getting reduced as pasted in graph May & June 2016

Can anybody please explain whats going wrong with my expression?

For Under limit -

count(if(monthname(Date#(ProjectMonth, 'MMM-YYYY'))< MonthName(today()) and aggr(sum({<PlanType={'Actuals'}>}HCM),[Resource ID] )<=0.7, [Resource ID],
if(monthname(Date#(ProjectMonth, 'MMM-YYYY'))>= MonthName(today()) and aggr(sum({<PlanType={'Allocation'}>}HCM),[Resource ID] )<=0.7, [Resource ID]
)
))

For Above limit -

count(if(monthname(Date#(ProjectMonth, 'MMM-YYYY'))< MonthName(today()) and aggr(sum({<PlanType={'Actuals'}>}HCM),[Resource ID] )>0.7, [Resource ID],
if(monthname(Date#(ProjectMonth, 'MMM-YYYY'))>= MonthName(today()) and aggr(sum({<PlanType={'Allocation'}>}HCM),[Resource ID] )>0.7, [Resource ID]

)

))

Re: Issues with Aggr function

PrashantSangle — Mon, 16 May 2016 08:54:13 GMT

Hi,

try adding monthYear field which you are selecting in aggr()

aggr(YourExpression,dim1,monthYear)

Regards

Re: Issues with Aggr function

Anonymous — Mon, 16 May 2016 09:09:43 GMT

Thanks..It worked by includingProjectMonth in the aggr function

topic Re: Issues with Aggr function in QlikView

Issues with Aggr function

Re: Issues with Aggr function

Re: Issues with Aggr function