cancel
Showing results for
Did you mean:
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:
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:
\$(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:
// 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]
;
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],

Resident TempCalendar;
DROP Table TempCalendar;
END

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

Alias Date as \$(vDateField1);
NoConcatenate
MasterCalendar:

Drop table MasterCalendar_Temp;
ElseIf \$(vIntervalMatch) = 1 then
Join IntervalMatch (Date)

join (\$(vFactTable))

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:

Master_Table:
"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,
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