Accumulating active employee count between the dates.

[umamaheswarareddy]

Hi All,

I am facing challenge to get active employee count between the dates. Please find the requirements below.

EMP_DIM_KEY	EMP_STRT_DT	EMP_STATUS_DT	EMP_STATUS
1001	10/1/2018	10/1/2018	Pending
1001	10/1/2018	10/15/2018	Active
1001	10/1/2018	12/11/2018	Active
1001	10/1/2018	1/10/2019	Discontinue
1002	10/12/2018	10/12/2018	Pending
1002	10/12/2018	11/12/2018	Active
1002	10/12/2018		Active
1003	10/14/2018	10/14/2018	Pending
1003	10/14/2018	11/7/2018	Active
1003	10/14/2018	2/2/2019	Discontinue

Here, Based on EMP_STATUS_DT Employee 1001 is active in October 2018 and still active up to Dec 2018. Means we have to show this guy in Oct, Nov, Dec.

Same Way others to and do sum for total.

Expected Result.

Oct 2018 - 1 (1001)

Nov 2018 -3 (1001,1002,1003)

Dec 2018 -3 (1001,1002,1003)

Jan 2019 -2 (1002,1003)

Feb 2019 -1 (1003)

Mar 2019 -1 (1003)

Apr 2019 -1 (1003)

[sunny_talwar]

Would you be open to transforming your data in the script? By transforming the data, you would be making this problem much more simpler for yourself.

[umamaheswarareddy]

In any manner i want to show in the bar graph with count of active employees.

[sunny_talwar]

Try this script

Table:
LOAD EMP_DIM_KEY,
	 EMP_STRT_DT,
	 Date(If(Len(Trim(EMP_STATUS_DT)) = 0, Today(), EMP_STATUS_DT)) as EMP_STATUS_DT,
	 EMP_STATUS;
LOAD * INLINE [
    EMP_DIM_KEY, EMP_STRT_DT, EMP_STATUS_DT, EMP_STATUS
    1001, 10/1/2018, 10/1/2018, Pending
    1001, 10/1/2018, 10/15/2018, Active
    1001, 10/1/2018, 12/11/2018, Active
    1001, 10/1/2018, 1/10/2019, Discontinue
    1002, 10/12/2018, 10/12/2018, Pending
    1002, 10/12/2018, 11/12/2018, Active
    1002, 10/12/2018,  , Active
    1003, 10/14/2018, 10/14/2018, Pending
    1003, 10/14/2018, 11/7/2018, Active
    1003, 10/14/2018, 2/2/2019, Discontinue
];

FinalTable:
LOAD *,
	 Date(If(EMP_DIM_KEY = Previous(EMP_DIM_KEY), Peek('EMP_STATUS_DT'), Today())) as EMP_STATUS_DT_END
Resident Table
Order By EMP_DIM_KEY, EMP_STATUS_DT desc;

DROP Table Table;

FinalFinalTable:
LOAD *,
	 Date(EMP_STATUS_DT + IterNo() - 1) as DATE,
	 MonthName(EMP_STATUS_DT + IterNo() - 1) as MONTHYEAR,
	 If(Date(EMP_STATUS_DT + IterNo() - 1) = Floor(MonthEnd(EMP_STATUS_DT + IterNo() - 1)) or Date(EMP_STATUS_DT + IterNo() - 1) = Today(), 1, 0) as MONTH_END_FLAG
Resident FinalTable
While EMP_STATUS_DT + IterNo() - 1 <= EMP_STATUS_DT_END;

DROP Table FinalTable;

and the expression

=Count(DISTINCT {<EMP_STATUS = {'Active'}, MONTH_END_FLAG = {1}>} EMP_DIM_KEY)