SCD

[tyagishaila]

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,

[tresesco]

Straight Table

Dim1:MonthOfJoining

Dim2:Dept

Exp: Count( Distinct Emp)

[Chanty4u]

hi ,

PFA

[tyagishaila]

Thanks Tresco for immediate response.

But it will not cover all Months (Jan -to-Dec)

[Chanty4u]

it will. bcz  Jan is not availble in ur requrmnt so it is not shwng. 

i hve included jan in script see the result

[tyagishaila]

Hi Chanty,

This qvw is not opened in my PC.

Can you share script of this.

Thanks

[tresesco]

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.

[tyagishaila]

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

[pho3nix90 · Accepted solution]

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;

[tyagishaila]

Thanks Shaun