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