Days To Event Categorical Variable
- john Putzke
- Preston Shankle (Unlicensed)
Description
Use a calculated variable to display a categorical amount of time between two dates of interest.
How To
Table:
The final table could look like the below image. In this example, the column of time categories designate the number of weeks before a phone call is due.
Pick List:
A pick list is required to categorize the number of weeks or whatever time value is being used:
Calculating the amount of days:
The JavaScript function to calculate the days between the two dates :
function diffInDays(a, b)
{
var milliSecondsPerDay = 1000 * 3600 * 24;
var baselineDate = a;
var followUpDate = b;
var numDays = ((followUpDate - baselineDate) / milliSecondsPerDay);
return Math.round(numDays);
}
The diffInDays method calculates the number of milliseconds between the two dates and converts the number to days.
In order to convert milliseconds to days, a division of the number of milliseconds by the number of milliseconds per day is required.
Categorizing the amount of days into a pick list value:
The following code will calculate which amount of days should correspond with which particular pick list value:
var dateDifference = diffInDays(V3, V4);
var weeks = Math.floor((dateDifference / 7) + 1);
return weeks > 3 ? 3 : weeks;
End result:
The final code in this example:
