Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ahsanshahzad
Contributor II

How to Calculate New Hire vs Terminated Employees

Hi Everyone

I need something like this in qlik sense.

HR+Excel+Dashboard.png

I have managed to do something near to it but not the exact result

Untitled123.jpg

New hire is coming correct but Terminated employees are not right.

Problem: I have Hire Date and Terminated Date. So how can I use the dimension.

If I use Hire Date as dimension. then Hired Employee result comes correct. If I use Terminated Date as dimension then Terminated employee result comes correct.  How to show both at the same time??

Measure 1 :  Count({<[Employee Status]={'Current Employees'},[Hire Year]={"$(=(Max([Hire Year])))"}>} [Employee Number])

Measure 2 :  Count({<[Employee Status]={'Terminated Employees'},[Contract End Year]={"$(=(Max([Hire Year])))"}>} [Employee Number])*-1

Thank You

7 Replies
MVP
MVP

Re: How to Calculate New Hire vs Terminated Employees

Do you may be need this?

Count({<[Employee Status] = {'Terminated Employees'}, [Contract End Year] = {"$(=(Max([Contract End Year])))"}>} [Employee Number]) * -1

ahsanshahzad
Contributor II

Re: How to Calculate New Hire vs Terminated Employees

Hi Sunny

Thank You for your reply

But this is not working. By using this, the negative value does not appear.

MVP
MVP

Re: How to Calculate New Hire vs Terminated Employees

Would you be able to share a sample to look at this?

ahsanshahzad
Contributor II

Re: How to Calculate New Hire vs Terminated Employees

Previously I was using separate bar charts for Hired and Terminated . But I want to manage it in one bar chart.

Pic 1: Hired

Dimension : [Hire Month]

Measure: Count({<[Employee Status]={'Current Employees'},[Hire Year]={"$(=(Max([Hire Year])))"}>} [Employee Number])

Untitled1.jpg

Pic 2: Terminated

Dimension: [Contract End Month]

Measure: Count({<[Employee Status] = {'Terminated Employees'}, [Contract End Year] = {"$(=(Max([Hire Year])))"}>} [Employee Number])

Untitled1 - Copy.jpg

Separately it works fine. Please suggest me to manage it in one bar chart.

ahsanshahzad
Contributor II

Re: How to Calculate New Hire vs Terminated Employees

Hi Sunny

Please find the attached sample data.

MVP
MVP

Re: How to Calculate New Hire vs Terminated Employees

What is your chart dimension here??? From what I am seeing you might need to create a Canonical Date here to resolve this problem of yours

ahsanshahzad
Contributor II

Re: How to Calculate New Hire vs Terminated Employees

Hi Sunny

Thanks for your reply

I read about Cannonical Date and tried to work. But not got the exact result.

I am sharing qvf file and data. Please have a look.

What I need is, I want month wise data for the current year.