Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Im facing a problem with counting the amount of employees per department per month. The dataset looks like this:
IDEmployee | Department | Startingdate | Enddate |
1 | Sales | 1-1-2020 | 30-8-2020 |
2 | Logistics | 15-2-2020 | |
3 | Finance | 10-4-2020 | |
4 | Sales | 20-6-2020 | |
1 | Marketing | 30-8-2020 |
Als result I want the following pivot table:
Can any of you help me? Thnx! 🙂
Hi @SanWWWWW
Try this:
T1_tmp:
Load
IDEmployee,
Department,
Date(Date#(Startingdate,'DD-MM-YYYY')) as Startingdate,
if(len(trim(Enddate)) > 0,Date(Date#(Enddate,'DD-MM-YYYY')),date(today())) as Enddate;
Load * Inline [
IDEmployee, Department, Startingdate, Enddate
1, Sales, 1-1-2020, 30-8-2020
2, Logistics, 15-2-2020 ,
3, Finance, 10-4-2020,
4, Sales, 20-6-2020 ,
1, Marketing, 30-8-2020 ,
];
NoConcatenate
T1:
Load *,
if(IDEmployee=Previous(IDEmployee) and MonthName(Enddate)=Previous(Monthname(Startingdate)),Date(AddMonths(Enddate,-1)),Enddate) as New_Enddate
Resident T1_tmp order by IDEmployee,Startingdate desc;
Drop table T1_tmp;
Cal:
Load New_Date,
MonthName(New_Date) as MonthYear;
Load Date(MinDate + IterNo() -1 ) AS New_Date While (MinDate + IterNo() - 1) <= Num(MaxDate);
Load distinct
min(Startingdate) as MinDate,
Max(New_Enddate) as MaxDate
Resident T1;
T2:
IntervalMatch(New_Date)
Load
Startingdate,
New_Enddate
Resident T1;
Hi @SanWWWWW ,
You can use Pivot table.
and add dimensions and measure as below
Hi @SanWWWWW
Try this:
T1_tmp:
Load
IDEmployee,
Department,
Date(Date#(Startingdate,'DD-MM-YYYY')) as Startingdate,
if(len(trim(Enddate)) > 0,Date(Date#(Enddate,'DD-MM-YYYY')),date(today())) as Enddate;
Load * Inline [
IDEmployee, Department, Startingdate, Enddate
1, Sales, 1-1-2020, 30-8-2020
2, Logistics, 15-2-2020 ,
3, Finance, 10-4-2020,
4, Sales, 20-6-2020 ,
1, Marketing, 30-8-2020 ,
];
NoConcatenate
T1:
Load *,
if(IDEmployee=Previous(IDEmployee) and MonthName(Enddate)=Previous(Monthname(Startingdate)),Date(AddMonths(Enddate,-1)),Enddate) as New_Enddate
Resident T1_tmp order by IDEmployee,Startingdate desc;
Drop table T1_tmp;
Cal:
Load New_Date,
MonthName(New_Date) as MonthYear;
Load Date(MinDate + IterNo() -1 ) AS New_Date While (MinDate + IterNo() - 1) <= Num(MaxDate);
Load distinct
min(Startingdate) as MinDate,
Max(New_Enddate) as MaxDate
Resident T1;
T2:
IntervalMatch(New_Date)
Load
Startingdate,
New_Enddate
Resident T1;
Use below script
Test:
Load *, Month(presentDate) as Month;
Load ID,
Department,
StartingDate,
EndDate,
Date(AddMonths(StartingDate,IterNo()-1)) as presentDate
while AddMonths(StartingDate, IterNo()-1) <= EndDate;
Load ID,
Department,
Date(Date#(Startingdate,'D-MM-YYYY')) as StartingDate,
if(isnull(Enddate) or len(trim(Enddate))=0,YearEnd(Date#(Startingdate,'D-MM-YYYY')),Date(Date#(Enddate,'D-MM-YYYY'))) as EndDate
//if(isnull(Enddate) or len(trim(Enddate))=0,YearEnd(Today()),Date(Date#(Enddate,'D-MM-YYYY'))) as EndDate
Inline[
ID,Department, Startingdate, Enddate
1, Sales, 1-1-2020, 30-8-2020
2, Logistics, 15-2-2020,
3, Finance, 10-4-2020,
4, Sales, 20-6-2020,
1, Marketing, 30-8-2020,
];
in front end take pivot table
dimension :
Department
Month
Measure
Count(Distinct ID)
Regards,
Prashant Sangle
You will get a synthetic key error which is fine , it will not create any harm 🙂
Thnx, this works 🙂
Perhaps you can help me with another problem too. Now I have the numbers of employees per department per month, but i also want to show the numbers of full time employees (= 40 hours a week).
See below for an example of the data. There is a mutation for employee 2 of 36 hrs (= 0,9 FTE) to 40 hrs (=1 FTE). I want to show the right number of FTEs in the right month.
IDEmployee | HoursPerWeek | Startingdate | Enddate |
1 | 40 | 1-1-2020 | |
2 | 36 | 15-2-2020 | 30-6-2020 |
3 | 40 | 10-4-2020 | |
4 | 28 | 20-6-2020 | |
2 | 40 | 1-7-2020 |