Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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

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?

Not applicable

Re: Range Avg headcount question with set analysis

Anyone?

Not applicable

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

Not applicable

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.

Community Browser