4 Replies Latest reply: Mar 12, 2012 3:28 PM by Marc Livingston

# Range Avg headcount question with set analysis

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

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

Anyone?

• ###### Re: Range Avg headcount question with set analysis

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

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.