Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us March 10th, 7 ways modern analytics can help you take smarter action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ITMercon
Contributor II
Contributor II

How to calculate number of employees actives in certain periods of time

Hi everyone! I'm trying to calculate the number of employees that were actives in previous years. It's important to mention that we have a fiscal year which starts on october. I've set my master calendar but still not getting the information I need because I have to set up a starting point from 2014. I tried to make it as a measure too but it didn't work either.

This is my script of variables and master calendar>

SET vFactTable = [Master_Table];
SET vFiscal = 1;
SET vFiscalStartMonth = 10;
SET vDateField1 = ["Hire Date"];
SET vDateField2 = If(Isnull([Attrition Date]),Today(),[Attrition Date]);

if len('$(vDateField2)')=0 or '$(vDateField2)'='$(vDateField1)' then
let vDateField2 = '$(vDateField1)';
set vIntervalMatch = 0;
else
set vIntervalMatch = 1;
end if

Temp:
Load
min($(vDateField1)) as minDate,
max($(vDateField2)) as maxDate
Resident $(vFactTable);

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

IF num(Today())<$(varMaxDate) then
let varMaxDate = num(today());
End

MasterCalendar:
LOAD *,
// YEAR
InYear(Date,$(varMaxDate),0)*-1 AS _InCurrentYear,
InYear(Date,$(varMaxDate),-1)*-1 AS _InLastYear,
InYear(Date,$(varMaxDate),-1,$(vFiscalStartMonth))*-1 AS _InLastFiscalYear,
// QUARTER
Dual('Q' & Ceil(Month/3), Ceil(Month/3)) AS Quarter,
Ceil(Month/3) AS _QuarterNum,
Dual('Q' & Ceil([Fiscal Month]/3), Ceil([Fiscal Month]/3)) AS [Fiscal Quarter],
Ceil([Fiscal Month]/3) AS _FiscalQuarterNum,
// MONTH
Num(Month) AS _MonthNum,
If(InMonth(Date,$(varMaxDate),0) AND Date <= Date($(varMaxDate)),1,0) AS _InCurrentMTD,
InMonth(Date,$(varMaxDate),0)*-1 AS _InCurrentMonth,
If(InMonthToDate(Date,$(varMaxDate),-1),1,0) AS _InPriorMTD,
If(InMonth(Date,$(varMaxDate),-1),1,0) AS _InPriorMonth,
If(InMonthtoDate(Date,$(varMaxDate),-12),1,0) AS _InLastYearMTD,
Dual(Year & '/' & Month,MonthStart(Date)) AS [YearMonth],
AutoNumber(Month & Year,'MonthCounter') AS MonthID,
AutoNumber(Ceil(Month/3) & Year,'QuarterCounter') AS QuarterID,
Num([Fiscal Month]) AS _FiscalMonthNum,
Dual([Year] & '/' & [Fiscal Month],[Year] + (Mod(Month - $(vFiscalStartMonth), 12)+1)) AS [Fiscal YearMonth]
;
Load
TempDate AS Date,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
Dual(Month(TempDate),Mod(Month(TempDate) - $(vFiscalStartMonth), 12)+1) AS [Fiscal Month],
YearName(TempDate, 0, $(vFiscalStartMonth)) AS [Fiscal Year Long],
Year(AddMonths(TempDate,$(vFiscalStartMonth))) as [Fiscal Year],

Resident TempCalendar;
DROP Table TempCalendar;
END

If $(vIntervalMatch) = 0 then
Rename table MasterCalendar to MasterCalendar_Temp;

Alias Date as $(vDateField1);
NoConcatenate
MasterCalendar:
Load * Resident MasterCalendar_Temp;

Drop table MasterCalendar_Temp;
ElseIf $(vIntervalMatch) = 1 then
Join IntervalMatch (Date)
Load $(vDateField1),$(vDateField2) Resident $(vFactTable);

join ($(vFactTable))
Load * Resident MasterCalendar;

Drop table MasterCalendar;
End If

The output of the script is>

ITMercon_1-1605806298962.png

 

However I need this as a result: (I want to see my data from 2014)

ITMercon_0-1605806035857.png

 

I jus have master table with the employee information:

Master_Table:
LOAD
"Type of hiring",
"Type of change",
"Previous position",
"Date of change",
ID as ID,
[ID] & Status & "Hire Date"&[Attrition Date]&Name as KeyEmployee,
Name as Name,
"Position Code" as "Position Code",
Position as Position_Name,
"Hay Grade" as "Hay Grade",
If(Match([Hay Grade],8,9,10,11),'Operative',
If(Match([Hay Grade],12,13,14),'Administrative',
If(Match([Hay Grade],15,16),'Head of Area',
If(Match([Hay Grade],17,18,19),'Managers',
If(Match([Hay Grade],20,21,22,23,24,25,26,27,28,29,30),'Directors'))))) as Type_Of_Position,
Gender as Gender,
Country as Country,
"Date of Birth" as "Date of Birth",
Age(Today(2),[Date of Birth])as Age_Employee,
"Hire Date" as "Hire Date",
month("Hire Date") as C_Month,
If("Hire Date"<'10/01/2014','.Before 14-15',If("Hire Date">='10/01/2014'and "Hire Date"<='09/30/2015','14-15',
If("Hire Date">='10/01/2015' and "Hire Date"<='09/30/2016','15-16',If("Hire Date">='10/01/2016' and "Hire Date"<='09/30/2017','16-17',
If("Hire Date">='10/01/2017'and "Hire Date"<='09/30/2018','17-18',If("Hire Date">='10/01/2018'and "Hire Date"<='09/30/2019','18-19',
If("Hire Date">='10/01/2019'and "Hire Date"<='09/30/2020','19-20',If("Hire Date">='10/01/2020'and "Hire Date"<='09/30/2021','20-21')))))))) as Hire_Crop_Year,

"Attrition Date" as "Attrition Date",
If(IsNull("Attrition Date"),'Active','Inactive') as Employee_Status,
if(isnull([Attrition Date]),age(today(2),"Hire Date"),age([Attrition Date],"Hire Date")) as Seniority,

Month("Attrition Date") as "Attrition Month",
If("Attrition Date"<'10/01/2014','Before 14-15',If("Attrition Date">='10/01/2014'and "Attrition Date"<='09/30/2015','14-15',
If("Attrition Date">='10/01/2015' and "Attrition Date"<='09/30/2016','15-16',If("Attrition Date">='10/01/2016' and "Attrition Date"<='09/30/2017','16-17',
If("Attrition Date">='10/01/2017'and "Attrition Date"<='09/30/2018','17-18',If("Attrition Date">='10/01/2018'and "Attrition Date"<='09/30/2019','18-19',
If("Attrition Date">='10/01/2019'and "Attrition Date"<='09/30/2020','19-20',If("Attrition Date">='10/01/2020' and "Attrition Date"<='09/30/2021','20-21'))))))))
as Attrition_Crop_Year
// "Type",
// Motive
FROM [lib://AttachedFiles/BD - Informe de Gestion Sep 2020.xlsx]
(ooxml, embedded labels, header is 1 lines, table is BD);

 

Can anyone please guide me through this? I've tried this expressions as well but any of them are giving me the desired result

Count({$<[Fiscal Long Year]={">=$(varMinDate)"}, [Fiscal Long Year]={"<$(varMaxDate)"}>} distinct KeyEmployee)

 

 

Labels (3)
0 Replies