topic Re: Range Avg headcount question with set analysis in QlikView

Range Avg headcount question with set analysis

Fri, 09 Mar 2012 19:15:24 GMT

New Question regarding headcount. How can I enter the set analysis {<CalendarMonthAndYear=,CalendarMonthEnd=,CalendarMonthName=,CalendarYear=>} into the below formula and still have it work? The hire and inactive dates are not directly tied to the calendar. The dimension here is Monthand Year Descending. I tried inserting it after the sum but that did not work.

Rangeavg( Below (

sum(aggr((if(CalendarMonthEnd>=[Hire Date] and CalendarMonthEnd <=if(isnull([Inactive Date]),monthend([Active Date]),MonthEnd([Inactive Date])),1,0)),CalendarMonthEnd,EmployeeHead ))

,0,12) )

Edit:

The previous expression work well, just needed to go with a new approach to get the headcount:

Rangeavg( Below (count({<CalendarMonthAndYear=,CalendarMonthEnd=,CalendarMonthName=,CalendarYear=>}DISTINCT Employee),0,12) )

Solution:

Rangeavg(

Below(

sum({<CalendarMonthAndYear=,CalendarMonthEnd=,CalendarMonthName=,CalendarYear=>}

aggr(count({<CalendarMonthAndYear=,CalendarMonthEnd=,CalendarMonthName=,CalendarYear=>}DISTINCT if(CalendarMonthAndYear>=[Hire Date] and CalendarMonthAndYear <=monthend([Active Date]),EmployeeHead)),CalendarMonthAndYear,EmployeeHead)

)

,0,12)

)

Re: Range Avg headcount question with set analysis

Fri, 09 Mar 2012 21:16:32 GMT

Ok I put a sample together. I want the left table to be "Locked" to selection of the month and year, but not to company. the right table is free to be altered by the date selection.

Help Please?

Re: Range Avg headcount question with set analysis

Mon, 12 Mar 2012 12:06:17 GMT

Anyone?

Re: Range Avg headcount question with set analysis

Mon, 12 Mar 2012 13:49:07 GMT

I tried this but it just returns 0.

Rangeavg( Below (sum(aggr(

count({$<CalendarMonthEnd={'>=([Hire Date] ) <=$(if(Headstatus='A',monthend([Active Date]),MonthEnd([Inactive Date])))'}>}

DISTINCT EmployeeHead),CalendarMonthEnd,EmployeeHead )),0,12) )

Re: Range Avg headcount question with set analysis

Mon, 12 Mar 2012 19:28:19 GMT

Does anyone have any other ideas on getting a headcount based on my example where I can restrict selection based on set analysis?

I am lost on what to do.