Solved: Re: Active employee count for specific year - Qlik Community

Anonymous · ‎2017-02-03

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.

sunny_talwar · ‎2017-02-03

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])

View solution in original post

sunny_talwar · ‎2017-02-03

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?

Silambarasan1306 · ‎2017-02-03

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)

vinieme12 · ‎2017-02-03

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

Can you provide a sample

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

Anonymous · ‎2017-02-03

yes you correct sorry for that it should be 4

sunny_talwar · ‎2017-02-03

May be like this:

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

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

sunny_talwar · ‎2017-02-03

or this:

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

Anonymous · ‎2017-02-03

This is how dataset looks like.

Anonymous · ‎2017-02-03

This is how dataset looks like.

Anonymous · ‎2017-02-03

This is how dataset looks like.