Use Calendar as Dimension

[rittermd]

Here is my challenge.

I have employee data.  It contains the hire date and the termination date and employee-id.

I want to create a table where the dimension is the actual calendar year-month and show the total number hired and also terminated in that calendar month in the same chart.

I have a master calendar QVD that contains 2017-Jan, 2017-Feb, etc.

The hire dates and termination dates also have a year-month component.

This is probably not hard to do.  But I am apparently calendar challenged.

[Anonymous]

(Date( DateFieldHere, 'MMM-YY')) as MonthYear

Is this what you trying to achieve?

[rittermd]

I already have the fields formatted this way.

My challenge is that I need the hire date and the termination date to match up with the calendar date.

For example:

2017-July       5 Hired     3 Terminated   Net/Gain/Loss +2

I can do hired or terminated by themselves with no problem.  But when I try to put all of this in one chart I get stuck.

[sumanta12]

Hi,

HIRE_TERMINATE_DATE:

LOAD * Inline [

EMP_ID,HIRE_DATE, TERMINATE_DATE

1, MAR-2016, JAN-2017

2, APR-2016, FEB-2017

3, MAY-2017, MAY-2017

];

CALENDER_DATE:

LOAD * Inline [

DATE, EMP_ID

DEC-2016,1

AUG-2016,2

FEB-2017,3

];

Left join (CALENDER_DATE)

LOAD * resident HIRE_TERMINATE_DATE;

drop table HIRE_TERMINATE_DATE;

[MK9885]

Can we get sample data?

[rittermd]

The calendar table has no relation to employee id.  It just contains every day for x years in it.

[Mark_Little]

Hi Mark,

How is the source data structured?

I would be looking to do something in Script to address this.

I would want some Like

Emp ID	Type	Date
1	Hired	01/01/2017
2	Hired	01/01/2017
3	Hired	01/01/2017
1	Terminated	20/01/2017
1	Terminated	21/01/2017

Then you can build the calendar off the Date Column. Make a table with you Month year as you dimension and your measures would be

COUNT({<Type={'Hired'}>}Type) for Hires

COUNT({<Type={'Terminated'}>}Type) For terminations

Mark

[rittermd]

If only it were that easy.

I have a single record for each employee that contains their ID, Hire Date, and Termination Data as well as other HR data.

I guess if I have to I could create 2 other tables from the above that contain the EmployeeID and HIre date in one and EmployeeID and termination date in the other to accomplish what you are saying.

[Mark_Little]

Hi Mark,

That's the way i would approach it, the added ability would be your calendar would drive both your dates and then In tour set analysis you can choose the data you're interested in by add Type={'Hired'} or Type={'Terminated'}.

I always try and do the heavy lifting in script and keep the set analysis as simple as possible.

Mark

[Quy_Nguyen]

Hi Mark,

Why dont you pre-calculate it in the script.

I attached an example to calculate the total of hired, terminated employee by month. After that you can easily use it to associate with your calendar.

Best