Headcount trend analysis problem

[#1]

Hello!

Hope you can help me with suggesting appropriate method to resolve below problem.

1. I have list of employees, with Hire Date, Leave Date, and other dimensions

2. I got a requirement to generate below trend table:

2015

Q1

Q2

Q3

Q4

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Headcount

New joiners

Exits

I am struggling to find a proper and clean method of joining employee list with master calendar, so that I can easily distinguish joiners leavers and headcount in specific month...

Any ideas?

Edit:

I am attaching sample application... In there I linked Master calendar with Hire Date.

This is of course wrong, because I am not able to properly calculate leavers...

I am looking for different approach which will allow me to calculate everything properly

Regards!

[robert_mika]

From your data set create Year,Month,Quarter and the use Pivot table .

You can  then use  Year,Month,Quarter as horizontal dimensions.

[#3]

Hello,

Thanks for answer. I know QV objects and I know which to utilize I am asking about join between Employees and Master calendar. If I will use Hire date,  I will not be able to properly calculate Leavers...

Basically Leavers, Joiners, and Headcount are 3 expressions I will use for this pivot. But I am not able to properly join employees with calendar dimensions to do calculations properly.

[robert_mika]

Attached your app..

[#5]

Added to main post.

[Siva_Sankar]

Hi,

You can create a flag for leavers and joiners using the leaving date as like attached one. Am not sure about the difference between headcount and hoiner formula.