Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rittermd
Honored Contributor

Use Calendar as Dimension

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.

9 Replies
michaelblackbur
New Contributor III

Re: Use Calendar as Dimension

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


Is this what you trying to achieve?

rittermd
Honored Contributor

Re: Use Calendar as Dimension

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
Contributor II

Re: Use Calendar as Dimension

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;

Khan_Mohammed
Honored Contributor II

Re: Use Calendar as Dimension

Can we get sample data?

rittermd
Honored Contributor

Re: Use Calendar as Dimension

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

mark6505
Valued Contributor III

Re: Use Calendar as Dimension

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 IDTypeDate
1Hired01/01/2017
2Hired01/01/2017
3Hired01/01/2017
1Terminated20/01/2017
1Terminated21/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
Honored Contributor

Re: Use Calendar as Dimension

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.

mark6505
Valued Contributor III

Re: Use Calendar as Dimension

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

Highlighted
Quy_Nguyen
Contributor III

Re: Use Calendar as Dimension

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

Community Browser