topic Re: How to count the dif between two dates? in QlikView

How to count the dif between two dates?

Breezy — Fri, 04 Nov 2016 19:34:05 GMT

Hello,

I need to find out the difference between two dates.

Reference # Open Date Close Date

1 31-May-16 21-Jun-16

2 1-Jul-16 -

3 31 May-16 15-Jun-16

The format looks like: 30-Sep-16 for the dates. Then I would like to show in a chart format the amount of time the Reference #s were open. i have not worked with dates yet in Qlik.

Re: How to count the dif between two dates?

Anonymous — Fri, 04 Nov 2016 20:38:45 GMT

Are you looking for diffference in days or hours ?

Interval ( Open Date -Close Date ,'dd')

syntax: networkdays (start_date, end_date [, holiday])

networkdays (Open Date, Close Date )

Re: How to count the dif between two dates?

Breezy — Fri, 04 Nov 2016 20:47:06 GMT

Hi Allu,

I am seeking the difference in days.

Re: How to count the dif between two dates?

swuehl — Sat, 05 Nov 2016 11:40:44 GMT

First, you need to interpret the input record values for your two date fields as dates, either by setting the default date format correctly or using the date#() interpretation function:

Get the Dates Right

Why don’t my dates work?

Now your field values are duals, still showing the formatted date as text representation, but holding also a number you can use in dat calculations, e.g. for calculating the date difference, just subtract two date values.

Example script:

Set DateFormat = 'DD-MMM-YY';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

LOAD *,

[Close Date]-[Open Date] as Duration

INLINE [ // INLINE table only used for demonstration

Reference #, Open Date, Close Date

1, 31-May-16, 21-Jun-16

2, 1-Jul-16, -

3, 31-May-16, 15-Jun-16

];

Re: How to count the dif between two dates?

MarcoWedel — Sat, 05 Nov 2016 23:08:10 GMT

another solution might be:

LOAD *,
    [Close Date]-[Open Date] as DaysOpen;
LOAD [Reference #],
    Date(Date#([Open Date],'DD-MMM-YY'))                as [Open Date],
    Date(Alt(Date#([Close Date],'DD-MMM-YY'), Today())) as [Close Date]
INLINE [
    Reference #, Open Date, Close Date
    1,          31-May-16, 21-Jun-16
    2,            1-Jul-16, -
    3,          31-May-16, 15-Jun-16
];

hope this helps

regards

Marco

Re: How to count the dif between two dates?

Sun, 06 Nov 2016 03:34:42 GMT

Hi Brianna,

Its just simple as

Num(Close Date) - Num(Open Date) as Diff