Calculating age and tenure

[ypinchuk]

Hello,

For the SAC story, I need to calculate for any chosen date, not only for today/current server date:

• an employee's age based on the date of birth
• an employee's tenure based on the job starting date

My model has two dimensions: Employee and Date (day granularity) and several metrics like working hours, salary etc.

I take the Date of birth and the Job starting date from the source (HANA) and keep them as properties of the dimension Employee, so now it's of Text type. 

What's the best practice for doing this?

1. How should I keep these dates in the SAC model - as Date dimensions or as properties of the Employee dimension? I doubt making them Date dimensions as I'm concerned regarding the size, as the date of birth could be far longer in the past (in the previous century). And as properties, they could be only of Text type
2. How do we calculate this date difference from the chosen date (through input control) if the dates are text properties?

Many thanks,

Yuri 

[akhilgs]

Hi @ypinchuk if you are bring the DOB & DOJ as dimensions then during data prep phase of the modeler, you can use the build in date diff function to create another column were you could store the value for age/ tenure 

DATEDIFF([Date1],[Date2],"Day")

Here first argument is your present date, second can be either DOB or DOJ & 3rd argument can be in "MONTHS" or in your case "YEARS". Yes this would lead to some duplication of data but it's easier and would suit your need, the date dimension in SAC supports years starting from 1900, I think that would suffice. The drawback here is that you cannot give the user control over the from date, it will always be present.

You check this help topic also to do the same in the story rather than in the model.

Calculate the Time Interval Between Two Dates 

If you are to use the DOB or DOJ as a text property of employee dimension, you would need to use scripting to calculate the date difference, that too depends on the format of the DOJ & DOB, if there are spaces and or other redundant characters, parsing the string to date will fail NaN error. A sample code would look like 

//Get current date
var currentDate = new Date();
//Converting date to milli seconds for date diff calculation
var firstDate = currentDate.getTime();
//Getting results from table
var resultSet = Table_1.getDataSource().getResultSet();
//Getting the DOB property of employee dimension
var DOB = resultSet[0]."Your dimension ID without quotes".properties["Your property ID in quotes"];
//Parsing DOB string via Date function to get it as date object
var secondDate = Date.parse(DOB);
var dateDiff = firstDate - secondDate;
//Converting back to years
var diffInYears  = (dateDiff/(1000*60*60*24*365)).toFixed(0);

You would need a loop to get all the DOB/ DOJ and also need to have these dimension properties exposed in the table so as to use the above script snippet. If you have a planning model, go through the answers in the below link

How to use dimension properties in SAC AA script?