topic Re: How can we show opening and closing Head count MOM in QlikView

How can we show opening and closing Head count MOM

ajayagraa — Wed, 08 Aug 2018 06:28:51 GMT

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

Re: How can we show opening and closing Head count MOM

sunny_talwar — Wed, 08 Aug 2018 12:01:24 GMT

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

Re: How can we show opening and closing Head count MOM

ajayagraa — Wed, 08 Aug 2018 16:23:09 GMT

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

Re: How can we show opening and closing Head count MOM

sunny_talwar — Wed, 08 Aug 2018 16:30:00 GMT

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

Re: How can we show opening and closing Head count MOM

ajayagraa — Wed, 08 Aug 2018 16:38:10 GMT

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

Thanks

Ajay verma

Re: How can we show opening and closing Head count MOM

sunny_talwar — Wed, 08 Aug 2018 16:45:02 GMT

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

Re: How can we show opening and closing Head count MOM

ajayagraa — Wed, 08 Aug 2018 16:58:18 GMT

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

Re: How can we show opening and closing Head count MOM

sunny_talwar — Wed, 08 Aug 2018 17:13:30 GMT

Got it, I am working on it

Re: How can we show opening and closing Head count MOM

sunny_talwar — Wed, 08 Aug 2018 17:24:24 GMT

This?

Re: How can we show opening and closing Head count MOM

ajayagraa — Wed, 08 Aug 2018 17:43:44 GMT

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

Re: How can we show opening and closing Head count MOM

ajayagraa — Wed, 08 Aug 2018 17:51:49 GMT

are you checking ?

Re: How can we show opening and closing Head count MOM

sunny_talwar — Wed, 08 Aug 2018 18:14:52 GMT

Check now...

Re: How can we show opening and closing Head count MOM

ajayagraa — Thu, 09 Aug 2018 06:10:26 GMT

this is working only normal year but not working financial year calendar . can we do something in script side ?

Re: How can we show opening and closing Head count MOM

sunny_talwar — Thu, 09 Aug 2018 10:14:38 GMT

I don't see any field named Financial year in the sample file your provided . What do you expect me to do? Read your mind my friend to know what you might have and what you might want. Please provide all the details to get better help

Best,

Sunny

Re: How can we show opening and closing Head count MOM

ajayagraa — Thu, 09 Aug 2018 10:46:35 GMT

sunny , could you please create a financial year and then test your code ?

Re: How can we show opening and closing Head count MOM

sunny_talwar — Thu, 09 Aug 2018 10:48:26 GMT

Sorry, I cannot do your work. If you need help then please do your part and I will do mine. If you don't want to... then let's just hope someone else might be willing to help.

Best,

Sunny

Re: How can we show opening and closing Head count MOM

ajayagraa — Thu, 09 Aug 2018 11:15:24 GMT

Thanks buddy for your great help . I will try to resolve the rest part at mt own .

Thanks again

Ajay Verma

Re: How can we show opening and closing Head count MOM

sunny_talwar — Thu, 09 Aug 2018 11:16:57 GMT

No problem at all. Please close this thread when you have your answer.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Re: How can we show opening and closing Head count MOM

Kunal12164 — Fri, 01 Dec 2023 09:35:10 GMT

Is any way i do all the logic in scripting rather than frontend calculation to get a same output?

Re: How can we show opening and closing Head count MOM

PrashantSangle — Fri, 01 Dec 2023 09:40:49 GMT

@Kunal12164 , to get more help, I suggest , create new post.

Also provide some data to get more accurate answer. Without having knowledge of your data, it is difficult to answer on question.

As per my experience, most of the time yes it possible to move front end logic in backend.

Regards,

Prashant Sangle