How to define serving period of employee

[#1]

Hi guys,
I'm newbie about qlikview, i struggling quite some time.
It is simple, i want to know how many month&year of an employee serving period.

e.g.
John Doe
Employment Date: 20-06-2005
Resignation Date: 15-04-2008
Termination Date: N/A

John Smith
Employment Date: 25-09-2007
Resignation Date: N/A
Termination Date: 15-10-2007

Mick Jack
Employment Date: 01-01-2008
Resignation Date: N/A
Termination Date: N/A

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MMM-YYYY hh:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LET CurrentDate = Date(today(0));
LET CurrentMonth = Month(today(0));
LET CurrentYear = Year(today(0));

LOAD
 "No_" as EEno,
 "Name" as "Employee Name",
Date("Employment Date") as EmploymentDate,
Date("Resignation Date") as EmploymentDate,
Date("Termination Date") as EmploymentDate;
SQL SELECT *
FROM DATABASE.dbo."Server$Employee"

How do I know both John Doe & John Smith how many day DAY&MONTH&YEAR they serving at company? (ResignationDate or TermintationDate-EmploymentDate)
And Mick Jack how many DAY&MONTH&YEAR current working right now? (CurrentDate-EmploymentDate)

Frank

[Oleg_Troyansky]

simply subtract the two dates, and you'll get the number of days as an interval. To account for missing dates, you can use IF functions and check if certain date exists - something like this:

if(len(trim([Resignation Date])) > 0, [Resignation Date],
if(len(trim([Termination Date])) > 0, [Termination Date],
today() )) as [End Date]

[#3]

Dear Oleg T,
Many thank for replied by question, but is NOT working, can you give sample .qvw ?

Thanks

[pover · Accepted solution]

Frank,

Here's an example plus a way you can calculate the number of years, months and days. It is a day o two off sometimes, but hopefully it is good enough.

Load Emp,
StartDate,
EndDate,
EndDate-StartDate as [Total No of Days],
year(date(EndDate-StartDate))-1900 as [No of Years],
month(date(EndDate-StartDate))-1 as [No of Months],
day(date(EndDate-StartDate)) as [No of Days];
Load Emp,
StartDate,
date(if(len(trim(TerminationDate))>0,TerminationDate,
if(len(trim(ResignationDate))>0,ResignationDate,today()))) as EndDate
Resident Temp;

Regards.

[#5]

Dear Karl,
Thanks for give sample...
But it still NOT working..
I tried to use database from sql

Here my code

ODBC CONNECT TO DATABASESQL;

Load
 "Employment Date",
 "Termination Date",
 "Resignation Date",
 if(len(trim("Termination Date"))>0,"Termination Date", if(len(trim("Resignation Date"))>0,"Resignation Date"));

SQL SELECT *
FROM ENSURE.dbo."DATABASESQL$Employee";

LOAD
 today(0)-"Employment Date" as [Total No of Days],
 Year(date(today(0)-"Employment Date"))-1900 as [No of Years],
 Month(date(today(0)-"Employment Date"))-1 as [No of Months],
 Day(date(today(0)-"Employment Date")) as [No of Days];
SQL SELECT *
FROM ENSURE.dbo."DATABASESQL$Employee";

Anything wrong? Even it not showing up field on Employment Date, Termination Date, Resignation Date.

[:'(]

Thanks

Frenky

[pover]

Revise the script I sent you in the QVW because there are some differences. For example the column that starts with if(len(trim...) has no column name. Also, in the if try if(not isnull([Termination Date] or [Termination Date] <> '', [Termination Date],....).

It might be helpful to send a screenshot of what your seeing to get a better idea of whats going wrong.

Regards.

[#7]

Dear Karl,
With on blue data not showing up using for your formula. The red is using the formula.
If i'm not using your formula blue able to show up.

ODBC CONNECT TO DATABASESQL;

Load
 "Employment Date",
 "Termination Date",
 "Resignation Date",
 if(len(trim("Termination Date"))>0,"Termination Date", if(len(trim("Resignation Date"))>0,"Resignation Date"));

SQL SELECT *
FROM ENSURE.dbo."DATABASESQL$Employee";

LOAD
 today(0)-"Employment Date" as [Total No of Days],
 Year(date(today(0)-"Employment Date"))-1900 as [No of Years],
 Month(date(today(0)-"Employment Date"))-1 as [No of Months],
 Day(date(today(0)-"Employment Date")) as [No of Days];
SQL SELECT *
FROM ENSURE.dbo."DATABASESQL$Employee";

[#8]

Karl,
It works now!!

Thank you so much.

Problem solved.

the formula was correct

[#9]

Oh yah, little problem.

How do I exclude public holiday and wekends (sat & sun) 😞

[pover]

Hi Frank,

Use the networkdays function. For the 3rd parameter of holidays, I recommend using a variable so you don't have to repeat the list of holidays in every function.

So, the function looks like this networkdays(startDate,endDate,$(holidays))

The variable holidays contains a list like this

'01/01/2010', '15/03/2010', '01/04/2010', '02/04/2010'

Good luck.