Date substraction as a field

[#1]

Hi,

I have two fields Hiredate and Termination date.

I need one more field as Tenure,Which would be Termination_Dt-Hire_Dt.

I tried to use this expression in the LoadScript

Year

("Termination_Dt") -Year("Hire_Dt") . I am getting an error.

Please suggest.

Srihari

[#2]

Are you loading directly from that Excel file? When I loaded that file, the dates loaded as numeric values (representing the dates).

Changing your expression to this should fix it:

Year(Date(Termination_Dt)) - Year(Date(Hire_Dt)) As Tenure

You could also use the Date function around each of the original fields: Date(Hire_Dt) As Hire_Dt

Or you could probably even modify the format of the loaded Excel data in the Wizard.

[#3]

Hi Miller,

I am actually getting these values from a table. I have exported these values to excel.

I am trying to analyze the tenure of the employees in the organization.

I should be able to see a field Tenure - with Years as 1,2,3…If I select 3 I will be able to see the employees with 3 or more years with the organization.

I have modified the format in the excel to date.

Please suggest.

[#4]

The original file loaded fine when I used my updated expression. Did you try that expression when loading from your table?

What is happening when you try to load the data in your app? Are you getting an error? What is the error? Have you tried using the field names without the double-quotes?

When I loaded it and used my expression, I got integers in the Tenure field. The data format isn't really the problem as you can clean it up when you load it into QlikView.

[#5]

The doubel quotes played the magic.

Thanks Muller for getting me through this problem

[johnw]

I don't think you want to use a simple year subtraction to get tenure. If I was hired December 31, 2009 and terminated January 1, 2010, my tenure should probably be 0, not 1. I definitely did NOT work a full year. So I'd use the age() function:

age(Termination_Dt,Hire_Dt)

Also, you mention the number of years with the organization. Unless this file only includes terminated employees, or is updated daily with the current date, you wouldn't get good data for current employees using a termination date. I assume that's not an issue, though.