How can we show opening and closing Head count MOM

[ajayagraa]

Hi All

In one of the requirement I have to show opening and closing emp headcount on month on month basis in my dashboard . also I have to develop the water fall chart to show the current Month and current year opening and closing Emp headcount with hiring and leaving of that month . am using master calendar and on selection of that the chart will filter out accordingly .  formula to calculate the opening and closing are given below

Opening balance = closing of the last month

Hiring = Current month joining

Leaving = Current month Leaving

Closing - Opening + Joining -leaving

can someone help me to achieve this requirement

Month	Opening Balance	Hiring	Leaving	Closing
Apr-17	1234	10	5	1239
May-17	1239	5	4	1240
Jun-17	1240	20	10	1250
Jul-17	1250	25	4	1271
Aug-17	1271	20	10	1281
Sep-17	1281	10	10	1281
Oct-17	1281	40	20	1301
Nov-17	1301	10	5	1306
Dec-17	1306	25	8	1323
Jan-18	1323	32	12	1343
Feb-18	1343	12	6	1349
Mar-18	1349	28	20	1357
Apr-18	1357	5	20	1342
May-18	1342	9	5	1346
Jun-18	1346	0	0	1346

Attaches file have content below things EIN - Employee Code Flag Sal - for Active emp along with new status 'Active' Left - Leaving Joining - for New joining

Sorry use this data

[Ahidhar]

try this in load script

tab1:
load * Inline
[
S.No,FiscalMonthYear
1,Apr-2017
2,May-2017
3,Jun-2017
4,Jul-2017
5,Aug-2017
6,Sep-2017
7,Oct-2017
8,Nov-2017
9,Dec-2017
10,Jan-2018
11,Feb-2018
12,Mar-2018
13,Apr-2018
14,May-2018
15,Jun-2018
16,Jul-2018
17,Aug-2018
];
left join(tab1)
LOAD
"Fiscal Month Year" as FiscalMonthYear,
EIN,
NewStatus,
Flag
FROM [lib://DataFiles/RowData_full.xls]
(biff, embedded labels, table is Sheet1$);
NoConcatenate
tab2:
load *
resident tab1
order by S.No;
drop table tab1;

tab3:
Load
[S.No],FiscalMonthYear as F1,
count(DISTINCT EIN) as Hiring
resident tab2
where Flag='Hiring'
group by [S.No],FiscalMonthYear;

left join(tab3)
load
[S.No],FiscalMonthYear as F2,
count(DISTINCT EIN) as Left
resident tab2
where Flag='Left'
group by [S.No],FiscalMonthYear;
left join(tab3)
load
[S.No],FiscalMonthYear as F3,
count(DISTINCT EIN) as Balance
resident tab2
where NewStatus='Active'
group by [S.No],FiscalMonthYear;

let vopeningbalance=Peek('Balance',0,'tab3');

tab4:
load *,
if(rowno()=1,$(vopeningbalance)+Hiring-Left,peek(ClosingBalance)+Hiring-Left) as ClosingBalance,
if(rowno()=1,$(vopeningbalance),peek(ClosingBalance)) as OpeningBalnce
resident tab3;
drop table tab3;

tab5:
load
[S.No],
F1 as FiscalMonthYear,
OpeningBalnce,
if(isnull(Hiring),0,Hiring) as Hiring,
if(isnull(Left),0,Left) as Left,
ClosingBalance
resident tab4;
drop table tab4;
drop table tab2;