Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Would you be able to share the raw data behind this output you are looking to get?
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
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
Have attached the row data please check and if you have any question then let me know
Thanks
Ajay verma
Seems like the data is starting from Apr-2018... do you want me to assume that the Opening balance was 0 for Apr-2018?
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
Got it, I am working on it
This?
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