Hello All,
Please find the script below.. I want to get the count of no of employees joined on year and month selection.
In frontend I'm using monthendnumdate to make selections.
I used expressions like: =Count({<num_date_of_join={"=$(=max(monthendnumdatenum))"}>}Emp_Code), But gives accumulated count of previous month.
Script:
employee:
LOAD
Emp_Code,
COMPANY_CODE,
COMPANY_NAME,
Department,
Permanent_Contract,
DATE_OF_JOIN,
Num(DATE_OF_JOIN) as num_date_of_join,
Year(DATE_OF_JOIN) as date_join_year,
Month(DATE_OF_JOIN) as month_join_month,
Date(DATE_OF_JOIN,'MMM-YYYY') as join_year_month,
//Date(DATE_OF_JOIN,'DD/MM/YYYY') as date_link,
Status,
if(len(Last_working_day)=0,Today(),Floor(Num(Last_working_day))) as Last_working_day,
Floor(Num(if(len(Last_working_day)=0,Today(),Last_working_day))) as num_last_working_day,
// if(IsNull(Last_working_day),Today(),Last_working_day) as num_last_working_day,
Year(Date(if(len(Last_working_day)=0,Today(),Last_working_day),'DD/MM/YYYY')) as last_year,
Month(if(len(Last_working_day)=0,Today(),Last_working_day)) as last_month,
GRADE,
Level,
Associate,
CLIENT_NAME
FROM [lib://Data_Connection/PHL CoA Emp Data.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Employee data]);
******************Calender********************
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(DATE_OF_JOIN) as minDate, // Replace DateKey with your Date key field
Max(Last_working_day) as maxDate // Replace DateKey with your Date key field
Resident employee; // 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 DateKey,
Date(TempDate,'DD/MM/YYYY') as date_link,
Num(TempDate) as numdate,
Num(TempDate) as in_numdate,
// Replace DateKey with your Date key field
week(TempDate) as Week,
Year(TempDate) as Year,
Year(TempDate) -1 as [PriorYear],
Month(TempDate) as Month,
Date(TempDate,'MMM-YYYY') as Year_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;
******************Calculated Data************************
OrderLog:
Load
num_date_of_join as in_join,
//Date(MonthEnd(Date(num_date_of_join)),'MMM-YYYY') as num_DOJ_MY,
num_last_working_day as in_last,
Emp_Code
Resident employee;
EventLog:
Load
numdate as in_numdate,
Date(MonthEnd(Date(numdate)),'MMM-YYYY') as monthendnumdate,
DateKey as in_date,
Year as in_year,
Month as in_year_month
Resident MasterCalendar;
EventLog1:
Load
num(monthendnumdate) as monthendnumdatenum
resident EventLog;
IntervalMatch (in_numdate) LOAD in_join, in_last Resident OrderLog;
