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

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





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









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