Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

tahreen371
New Contributor III

How to get Count of active and Inactive employees

Hello All,

I'm having quite tough time in achieving this, plz Plz anyone help me on this.

I have attached the sample data.

I need to show Count of Active, Inactive and Joined employees with all the scripting in Backend because of performance issue and in frontend I could acheive the requirement.

Active Employees : If i select on the Feb-2005, it should show the Count of No of employees active from thr min date Feb-2002 till the selection made

Inactive Employees : for example, Emp_Code - H12 should show the count in inactive only if the selections made on or after this date - 04-09-2018
Till then the employee should be in active count.

Joined : Count of employees Joined per month or based on the selection.

Below is the script i have used till now for backend implementation.

Employee:
First 10
LOAD
Emp_Code,
COMPANY_CODE,
COMPANY_NAME,
Department,
Date(num(floor(DATE_OF_JOIN))) as DOJ,
Date(num(floor(Last_working_day))) as LWD,
if(Isnull(Last_working_day),1,2) as Status_check,
if(IsNull(Last_working_day),Today(),Date(Floor(Num(Last_working_day)),'DD/MM/YYYY')) as new_Last_working_day,
// if(Isnull(Last_working_day),'Active','Inactive') as Status
//if(len(Last_working_day)=0,'Active','Inactive') as Status_Check
// Level,
// Associate,
CLIENT_NAME
// "Client code"
FROM [lib://Data_Connection/PHL CoA Emp Data.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Employee data]);

 

CalendarLinkage:
LOAD
// Load Table Keys
Date(Num(Floor(DOJ))) As [Key Master_Date Date],
// Load Table Data
Emp_Code,
DOJ As Date,
'JoiningDate' As DateType
Resident Employee;

Concatenate (CalendarLinkage)
LOAD
// Load Table Keys
Date(Num(Floor(LWD))) As [Key Master_Date Date],
// Load Table Data
Emp_Code,
LWD As Date,
'LeavingDate' As DateType
Resident Employee
Where Not (IsNull(LWD));

QuartersMap:
MAPPING LOAD * Inline [
Month, Q
1, Q1
2, Q1
3, Q1
4, Q2
5, Q2
6, Q2
7, Q3
8, Q3
9, Q3
10, Q4
11, Q4
12, Q4
];

MonthMap:
Mapping LOAD * Inline [
MonthNo, LongMonth
1, January
2, February
3, March
4, April
5, May
6, June
7, July
8, August
9, September
10, October
11, November
12, December
];

Temp:
Load
min([Key Master_Date Date]) as minDate, // Replace DateKey with your Date key field
Max([Key Master_Date Date]) as maxDate // Replace DateKey with your Date key field
Resident CalendarLinkage; // Replace SourceTable with the name of the table containing the DateKey field

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Set vFM = 4 ;

MasterCalendar:
LOAD
TempDate AS [Key Master_Date Date],
Date(TempDate,'DD/MM/YYYY') as date_link,
Num(TempDate) as numdate,
Date(TempDate,'MMM-YYYY') as Month_Year,
// Replace DateKey with your Date key field
week(TempDate) as Week,
Year(TempDate) as Year,
Year(TempDate) -1 as [PriorYear],
Month(TempDate) as Month,
Dual(ApplyMap('MonthMap', Month(TempDate), Null()), Month(TempDate)) as MonthLong,
Day(TempDate) as Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
// Fiscal Year fields
//Year(TempDate) + If(Month(TempDate)>=$(vFM), 1, 0) As [Fiscal Year],
//Mod(Month(TempDate)-$(vFM), 12)+1 As [Fiscal Month]
Resident TempCalendar
Order By TempDate ASC;

Drop Table TempCalendar;
exit script;

4 Replies
Highlighted
mdmukramali
Valued Contributor III

Re: How to get Count of active and Inactive employees

Hi,

 

Maybe like attached sample file.

 

 

Thanks,

Mohammed Mukram Ali

tahreen371
New Contributor III

Re: How to get Count of active and Inactive employees

This works great, thank you so much Ali, But i'm really very poor at loops.

Can you please tell me how can I get Inactive employees count and also Joined employees count which should work for month selection as well.

I used : Month (DATE_OF_JOIN +IterNo()-1) as Month, But the values do not vary or change for Month selection.

Regards,
Tahreen

tahreen371
New Contributor III

Re: How to get Count of active and Inactive employees

Hello Ali,

And also when i select year 2018, the active count should come down to 11 but that does not happen.

Is there any way?? Plz

Reagards

tahreen371
New Contributor III

Re: How to get Count of active and Inactive employees

Anyone any update on this plz??