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.

Join us March 10th, 7 ways modern analytics can help you take smarter action. REGISTER NOW
Showing results for 
Search instead for 
Did you mean: 
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;
set vIntervalMatch = 1;
end if

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;

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

InYear(Date,$(varMaxDate),0)*-1 AS _InCurrentYear,
InYear(Date,$(varMaxDate),-1)*-1 AS _InLastYear,
InYear(Date,$(varMaxDate),-1,$(vFiscalStartMonth))*-1 AS _InLastFiscalYear,
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,
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]
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;

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

Alias Date as $(vDateField1);
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>



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



I jus have master table with the employee information:

"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