15 Replies Latest reply: Feb 3, 2017 8:18 AM by Jayesh Mule

# Active employee count for specific year

Hello,

I have count of people who are starting and who are terminating year wise. I want the active employee count as per the year if I select year from filter pane. Please find attached image for more information.

• ###### Re: Active employee count for specific year

How are you getting 5 for 2002? Total Started is 2 + 3 = 5 and 1 guy left.... Should it be 4? If it is 5, can you explain the logic?

• ###### Re: Active employee count for specific year

yes you correct sorry for that it should be 4

• ###### Re: Active employee count for specific year

May be like this:

Sum({<Year = {"\$(='<=' & Max(Year))"}>}[Employee Who Started]) -

Sum({<Year = {"\$(='<=' & Max(Year))"}>}[Employee Who Terminated])

• ###### Re: Active employee count for specific year

or this:

Sum({<Year = {"\$(='<=' & Max(Year))"}>} RangeSum([Employee Who Started] - [Employee Who Terminated]))

• ###### Re: Active employee count for specific year

This is how dataset looks like.

• ###### Re: Active employee count for specific year

When did EMP ID 6 started? he is getting terminated in 2002?

• ###### Re: Active employee count for specific year

Oops actually i cant share complete dataset due to company issue but i want approach to get the active employee count you can assume he started in 2001 like that.

• ###### Re: Active employee count for specific year

We are not looking for your actual data my friend.... we are looking for a representative data which is close to your actual data. I already provided one solution based on what you shared... I am going to provide another... but if that doesn't work... I am afraid I am not going to attempt again unless you provide a sample which is more inline with your actual data

Count(DISTINCT {<Year = {"\$(='<=' & Max(Year))"}, Attribute = {'StartYear'}>}[EMP ID]) -

Count(DISTINCT {<Year = {"\$(='<=' & Max(Year))"}, Attribute = {'Terminate Year'}>}[EMP ID])

• ###### Re: Active employee count for specific year

This option is correct but when I select 2002 it shows 3 rather than showing 5 because till 2002 5  employees were active and after 2002 1 left.

• ###### Re: Active employee count for specific year

I think it should show 5 because assuming you have another row for EMP ID = 6 for start year... we would count 6 for start year and 1 for terminate year.... 6 - 1 = 5.

• ###### Re: Active employee count for specific year

Got that IT worked perfectly.............!!!!

• ###### Re: Active employee count for specific year

Use Set Analysis,

Try the below expression,

Count({<Year={2002}>}Distinct Employee)

If u want to see all the separate year listed,

Count({<Year={\$(=Max(Year))>}Distinct Employee)

• ###### Re: Active employee count for specific year

This is how dataset looks like.

• ###### Re: Active employee count for specific year

Is this the exact representation of the dataset you are working with? I don't think so

Can you provide a sample

• ###### Re: Active employee count for specific year

This is how dataset looks like.