Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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)

)

4 Replies
Not applicable
Author

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?

Not applicable
Author

Anyone?

Not applicable
Author

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

Not applicable
Author

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.