Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community Members,
Below is the sample data,
Emp MonthOfJoning Dept
101 Feb D1
101 Apr D2
101 Sep D4
102 Feb D2
102 Oct D3
103 Mar D4
104 Apr D3
104 Sep D2
104 Dec D4
I need to find for each month how many employees were working in each department.
Thanks,
Please find attached.
The model checks the start of the emp in the specific department and then calculates an end date depending if the employee belonged to another department previously. It then calculates how many employees was active within a department over months.
Also deselect "Suppress zero-values" on Presentation tab on the chart
Scripts:
Data:
LOAD
Emp,
Date(Date#(MonthOfJoning,'DD-MMM-YYYY')) as MonthOfJoning,
Dept
;
LOAD * INLINE [
Emp, MonthOfJoning, Dept
101, 01-Feb-2016, D1
101, 01-Apr-2016, D2
101, 01-Sep-2016, D4
102, 01-Feb-2016, D2
102, 01-Oct-2016, D3
103, 01-Mar-2016, D4
104, 01-Apr-2016, D3
104, 01-Sep-2016, D2
104, 01-Dec-2016, D4
];
Left Join
LOAD MonthOfJoning, if(Peek('Emp')=Emp,if(isnull(Peek('MonthOfJoning')),Date(AddMonths(MonthOfJoning, 999)),Peek('MonthOfJoning')),Date(AddMonths(MonthOfJoning, 999))) as End,Emp Resident Data Order By Emp, MonthOfJoning desc;
MinMax: LOAD Min(MonthOfJoning) as MinDate, Max(MonthOfJoning) as MaxDate Resident Data;
LET vMinDate = YearStart(Peek('MinDate'));
LET vMaxDate = Peek('MaxDate');
//DROP Table MinMax;
Calendar:
LOAD
Date('$(vMinDate)'+RecNo()) as Date,
Month('$(vMinDate)'+RecNo()) as Month,
Date#(Text(Date('$(vMinDate)'+RecNo(),'MMM YYYY')),'MMM YYYY') as YearMonth
AutoGenerate $(vMaxDate)-$(vMinDate);
DROP Table MinMax;
IntervalMatch(Date)
LOAD MonthOfJoning, End Resident Data;
Straight Table
Dim1:MonthOfJoining
Dim2:Dept
Exp: Count( Distinct Emp)
hi ,
PFA
Thanks Tresco for immediate response.
But it will not cover all Months (Jan -to-Dec)
it will. bcz Jan is not availble in ur requrmnt so it is not shwng.
i hve included jan in script see the result
Hi Chanty,
This qvw is not opened in my PC.
Can you share script of this.
Thanks
Assuming you meant all months for all departments. Right, because you don't have data for all months for all departments. You have to generate the data at the back end. Check: How to populate a sparsely populated field for similar way.
I also need Emp must belong to same dept until its dept is not changed.
e.g. Emp 101 belongs to D1 in Feb and Mar Month
& in Apr, May Jun, Jul, Aug he is the employee of dept D2.
& in Sep, Oct, Nov and Dec belongs to D4 dept.
Hope my problem is more clear now.
Thanks
Please find attached.
The model checks the start of the emp in the specific department and then calculates an end date depending if the employee belonged to another department previously. It then calculates how many employees was active within a department over months.
Also deselect "Suppress zero-values" on Presentation tab on the chart
Scripts:
Data:
LOAD
Emp,
Date(Date#(MonthOfJoning,'DD-MMM-YYYY')) as MonthOfJoning,
Dept
;
LOAD * INLINE [
Emp, MonthOfJoning, Dept
101, 01-Feb-2016, D1
101, 01-Apr-2016, D2
101, 01-Sep-2016, D4
102, 01-Feb-2016, D2
102, 01-Oct-2016, D3
103, 01-Mar-2016, D4
104, 01-Apr-2016, D3
104, 01-Sep-2016, D2
104, 01-Dec-2016, D4
];
Left Join
LOAD MonthOfJoning, if(Peek('Emp')=Emp,if(isnull(Peek('MonthOfJoning')),Date(AddMonths(MonthOfJoning, 999)),Peek('MonthOfJoning')),Date(AddMonths(MonthOfJoning, 999))) as End,Emp Resident Data Order By Emp, MonthOfJoning desc;
MinMax: LOAD Min(MonthOfJoning) as MinDate, Max(MonthOfJoning) as MaxDate Resident Data;
LET vMinDate = YearStart(Peek('MinDate'));
LET vMaxDate = Peek('MaxDate');
//DROP Table MinMax;
Calendar:
LOAD
Date('$(vMinDate)'+RecNo()) as Date,
Month('$(vMinDate)'+RecNo()) as Month,
Date#(Text(Date('$(vMinDate)'+RecNo(),'MMM YYYY')),'MMM YYYY') as YearMonth
AutoGenerate $(vMaxDate)-$(vMinDate);
DROP Table MinMax;
IntervalMatch(Date)
LOAD MonthOfJoning, End Resident Data;
Thanks Shaun