topic Re: Calculate Age (Days) Between 2 Dates in QlikView

Calculate Age (Days) Between 2 Dates

jmonroe918 — Sat, 25 Jan 2014 01:29:20 GMT

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:

What is the expression for calculating the date difference to determine the age of each record?
How do I incorporate that into the load code?

Thanks.

Jeff

Re: Calculate Age (Days) Between 2 Dates

brindlogcool — Sat, 25 Jan 2014 02:20:49 GMT

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.

Re: Calculate Age (Days) Between 2 Dates

Sokkorn — Sat, 25 Jan 2014 02:31:33 GMT

Hi Jeff,

Simply use this in load script CloseDate-CreateDate as TotalDay

Regards,

Sokkorn

Re: Calculate Age (Days) Between 2 Dates

Sat, 25 Jan 2014 03:31:21 GMT

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.

Re: Calculate Age (Days) Between 2 Dates

nilesh_gangurde — Sat, 25 Jan 2014 05:05:56 GMT

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

Re: Calculate Age (Days) Between 2 Dates

jagan — Sat, 25 Jan 2014 05:22:37 GMT

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.

Re: Calculate Age (Days) Between 2 Dates

sunilkumarqv — Sat, 25 Jan 2014 05:44:29 GMT

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

Re: Calculate Age (Days) Between 2 Dates

jmonroe918 — Sat, 25 Jan 2014 22:05:39 GMT

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
'

Re: Calculate Age (Days) Between 2 Dates

jmonroe918 — Sat, 25 Jan 2014 22:06:37 GMT

or actually use an if/then statement.

Jeff

Re: Calculate Age (Days) Between 2 Dates

brindlogcool — Sat, 25 Jan 2014 23:39:15 GMT

You can use the class function to group the ages.

Re: Calculate Age (Days) Between 2 Dates

Anonymous — Sun, 26 Jan 2014 15:35:24 GMT

Hi Jeffrey,

Use below load script:

Let vStartDate = MM/DD/YYYY;

Let vEndDate = MM/DD/YYYY;

Data:

LOAD

StartDate,

EndDate,

($(DateDiff(StartDate>$ (vStartDate) , EndDate <=$(vEndDate))) AS AGE

FROM DataSource;

Re: Calculate Age (Days) Between 2 Dates

Sokkorn — Mon, 27 Jan 2014 04:14:00 GMT

Hi Jeff,

May we can try this:

[TableName]:

Load *,

If(DayOpen<30,'<30',If(DayOpen<61,'31-60',If(DayOpen<121,'91-121',If(DayOpen>120,'>120')))) as [OpenBucket];

Load

ID,

Status,

CreateDate,

CloseDate,

Floor(Today()-CreateDate) as DayOpen;

Select ID, Status, CreateDate, CloseDate From TableName;

Regards,

Sokkorn