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

[sunny_talwar]

Would you be able to share the raw data behind this output you are looking to get?

[ajayagraa]

it would be little bit difficult to share the payrol data . can you please take dummy  data to create this requirement ? we have three dates in my table DOJ , Date of releasing (DOR) and Sal Date.

Thanks in Advance

ajay

[sunny_talwar]

can you please take dummy  data to create this requirement ?

Hey Ajay -

Where is the dummy data? Isn't what you posted above the output you are hoping to get? I am not sure what should I load this as... if I directly load the above data.. I am sure enough going to be display the above output... but I am not sure how will that help you my friend.

I want to help you, but you will have to share more than this. See if these threads help

Preparing examples for Upload - Reduction and Data Scrambling

Qlik Community Tip: How to Get Answers to Your Post

[ajayagraa]

Have attached the row data please check and if you have any question then let me know

Thanks

Ajay verma

[sunny_talwar]

Seems like the data is starting from Apr-2018... do you want me to assume that the Opening balance was 0 for Apr-2018?

[ajayagraa]

Please use the new file , data will start from Apr 2017

Opening Head Count of Apr 2017 = Total active emp of Apr 2017

Closing Head Count of Apr 2017 -  (Total active emp of Apr 2017 + Total New Joiner in Apr 2017) - Total Leaving of Apr 2017

Thanks

Ajay Verma

[sunny_talwar]

Got it, I am working on it

[sunny_talwar]

This?

[ajayagraa]

Thanks sunny , for you help . It is working when we have no selection in fiscal year but when i select fiscal year 2018 it is giving Apr 2018 opening HC - 1156 while it should be closing of Mar-2017 - 1170 .

Thanks

Ajay