Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have two dates: CreateDate and CloseDate and would like to calculate the age of the record that contains these two dates. My questions are:
Thanks.
Jeff
You can use the networkDays(startdate,enddate,[holidays]) to calculate the age. It calculates number of working days between startdate(should be less than the end date) and enddate,holiday dates is optional you can include it if required.
Hi Jeff,
Simply use this in load script CloseDate-CreateDate as TotalDay
Regards,
Sokkorn
Hi Jeffrey, If you want calculate the age in no of days, you can directly subtract the date values like below:
Num(CloseDate) - Num(CreateDate) AS Age_in_Days
If you want exclude the Holidays please use Networkdays function.
Hi,
You can use the interval Function.
interval(expression [ , format-code ])
The interval function formats the expression as a time interval according to the string given as a format-code. If the format code is omitted, the time format set in the operating system is used. Intervals may be formatted as a time, as days or as a combination of days, hours, minutes, seconds and fractions of seconds.
Examples:
interval( A-B, 'D hh:mm' ) where A=97-08-06 09:00:00 and B=96-08-06 00:00:00 returns:
String | 365 09:00 |
Number | 365.375 |
-Nilesh
Hi,
Try like this
Data:
Load
*,
Interval(EndDate - StartDate, 'DD') AS Age
FROM DateSource;
Replace EndDate and StartDate with your actual field names.
Regards,
Jagan.
Here you can do in two ways one using Interval and using DateDiff .
Data:
Load
*,
Interval(EndDate - StartDate, 'DD') AS Age
Resident oldData;
Replace EndDate and StartDate with your actual field names.
//Function to calculate Date Difference in script
SET DateDiff = Num(((StartDate) - (EndDate)) + 1);
Now, we can use the MonthDiff as a function
Data:
LOAD
UniqueNo,
StartDate,
EndDate,
($(DateDiff(StartDate, EndDate)) AS DateDifference
FROM DataSource;
In the above load statement we are calculating the months difference between two dates and arriving it as a new column.
We can also use the MonthDiff in expressions also like below
=$(DateDiff(Today(), Today() + 65))
Hope this helps others.
Regards,
Sunil
Sokkorn:
That was exaclty what I wanted. Thank you.
Now what I want to do is to subtract the current date (today) from the "Create Date" (the age of open records) and then create a field called "Days Open". And in the field I want, based on the calculated age, inserted either "<30", "31-60", "61-90", "91-120" and ">120".
I will then use the "Days Open" data into a bar chart that will count the number of each range of open records. Closed records (with a closed date) do not get counted.
I know I want to use Interval Match, but not sure how to incorporate.
Jeff
'
or actually use an if/then statement.
Jeff
You can use the class function to group the ages.