8 Replies Latest reply on Jul 24, 2018 11:57 PM by Mark Kretschmer

Counting employees

Hi there,

I got stuck with the following. I got a list of our companies employees, divded by branches. For each there is at least one start date (day the entered the company) and one end date (date they left the company). For some, there are more than one start and/or one end date, e.g. when the changed the branches of the company.

I was able to create a view that shows, using gantt charts, the start date, end date and the tenure of each employee. But I am not able to realize I feature I am asked to provide:

My question is: is there a way to count all active employees on a given date? E.g. the 1st Janauary each year?

Best,

Mark

• 1. Re: Counting employees

Hi Mark,

Could you please share some sample data.

• 2. Re: Counting employees

Hi Mark,

You can easily do that by using below formula:

let say as_of_date is your parameter on which you need no of employees. create calculated column as :

#as_of_date# >=start_date and #as_of_date# <= end_date

This will be binary filter (true or false). When you will choose true and do the count of employees, You will get your number.

Hope this helps.

Thanks,

Ankit Bansal

1 of 1 people found this helpful
• 3. Re: Counting employees

Thanks Ankit, very helpfull indeed! It leads me to a second thought:

Do you know a way to set up a bar chart with a bar for each year, counting the employees?

• 4. Re: Counting employees

But how would you count number of employees for an year. Like some employee joined on let say 1 july 2018 and left on 1-aug-2018, some might have joined on 1- jan-2017 and not yet left and someone joined in 2018 and not yet left.

So when you are counting employees for 2018 , how would you consider?

• 5. Re: Counting employees

That is the reasn why I would like to use a qualified date, e.g. 1st January. Somebody who joined in July and left in August would not be counted. For 2018 I would count every employee who was employed on the first of Janauary 2018.

• 6. Re: Counting employees

You can only count no of  employees on a given date. So if you want year wise count , you may want to see number of employees at the end of each year ie: 31st DEC of each year.

• 7. Re: Counting employees

Ok in that case one way to do is something like below:

create a table/excel which will have 1st day of all years like :

date_of_emp_count

01-01-2012

01-01-2013

01-01-2015

01-01-2015

01-01-2016

01-01-2017

01-01-2018

Now join this table with your original table with join as 1=1(create calculated join with value 1)

the create calc field and put it as filter (true)

date_of_emp_count>=start_date and date_of_emp_count<=end_date

then keep the date_of_emp_count on column shelf as year and count(emp) on row shelf to get the bar chart.

1 of 1 people found this helpful
• 8. Re: Counting employees

Hey Mark,

Thanks,

Ankit Bansal

• 9. Re: Counting employees

Hey Ankit,

thanks a lot! That solved my problem!

Best,

Mark