Counting employees per month/department

[SanWWWWW]

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! 🙂

[abhijitnalekar]

Hi @SanWWWWW ,

You can use Pivot table.

and add dimensions and measure as below

[jyothish8807 · Accepted solution]

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;

[PrashantSangle]

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

[jyothish8807]

You will get a synthetic key error which is fine , it will not create any harm 🙂

[SanWWWWW]

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