topic Re: Difference between two dates in QlikView

Difference between two dates

Mon, 16 Jul 2012 10:51:33 GMT

I would like to know if it is possible with a formula to calculate the difference between two dates.

For example, I have these two dates: 2012-07-05 and 2012-07-11

Now in my chart there should be the number 6 to get to know that for example one order is in progress for 6 days.

Can anyone explain how to do it?

Thanks a lot!

Re: Difference between two dates

m_woolf — Mon, 16 Jul 2012 10:59:09 GMT

If these are dates and not timestamps, you should be able to use Field1 - Field2.

If they are timestamps, then somthing like floor(Field1)-floor(Field2)

Re: Difference between two dates

Mon, 16 Jul 2012 11:15:34 GMT

Thank you for your answer but I don't have separate fields. It is just one field with dates from April to today. Is it just possible with two fields?

Re: Difference between two dates

m_woolf — Mon, 16 Jul 2012 11:17:18 GMT

How will you know which two dates you want to subtract?

Re: Difference between two dates

Mon, 16 Jul 2012 11:22:38 GMT

Put the date into two fields date1 and date2

and use mwoolf's formula to calcualte the difference

floor(date1) - floor(date2)

Re: Difference between two dates

m_woolf — Mon, 16 Jul 2012 11:38:10 GMT

Are you trying to do something like my attachment?

Re: Difference between two dates

Mon, 16 Jul 2012 11:53:32 GMT

Sorry, but I can't open your attachment. Could you write your example here?

Re: Difference between two dates

m_woolf — Mon, 16 Jul 2012 11:56:39 GMT

Here is a screenshot:

Re: Difference between two dates

ToniKautto — Mon, 16 Jul 2012 11:58:12 GMT

As said above you should split the data in your load script, and format the values accordingly to Date values.

For example if your source data is according to the DateFormat system varaible then the principle could be as below;

T1:

LOAD

EndDate-StartDate AS IntervalLength

;

LOAD

DateInterval,

SubField(DateInterval, ' - ', 1) AS StartDate,

SubField(DateInterval, ' - ', 2) AS EndDate

Inline [

DateInterval

2012-07-05 - 2012-07-11

];

If your source needs to be reformatted to match the DateFormat variable then the principle below should apply;

T2:

LOAD

EndDate-StartDate AS IntervalLength

;

LOAD

DateInterval,

Date(Date#(StartDate, 'YYYY/MM/DD')) AS StartDate,

Date(Date#(EndDate, 'YYYY/MM/DD')) AS EndDate

;

LOAD

DateInterval,

SubField(DateInterval, ' - ', 1) AS StartDate,

SubField(DateInterval, ' - ', 2) AS EndDate

Inline [

DateInterval

2012/05/05 - 2012/06/05

];

The DateFormat system varibale is the format included at the beginning of your script;

SET DateFormat='YYYY-MM-DD';

Re: Difference between two dates

Mon, 16 Jul 2012 13:23:48 GMT

Thank you for your answers but now we found another solution:

=( subfield('0;31;59;90;120;151;181;212;243;273;304;334;365',';',num(month(date#(max(field1),'YYYYMMDD'))) )

+num(day(date#(max(field1),'YYYYMMDD'))) )

- (subfield('0;31;59;90;120;151;181;212;243;273;304;334;365',';',num(month(date#(min(field1),'YYYYMMDD'))) )

+num(day(date#(min(field1),'YYYYMMDD'))) )

Now we have just little problems when the months overlap.

Re: Difference between two dates

ToniKautto — Mon, 16 Jul 2012 18:36:39 GMT

It is really recommend that you format your Date field properly in the load script. This way you can utilize the QlikView date and time functions and make a more efficient and easy to overview implementation.

Keep in mind that a Date value has a numerical part that corresponds to the number of days since 1899-12-30, which means that a larger Date minus a smaller Date will result in the number of days between the Dates.

I do not really see the purpose of the subfield, the following should work to get the difference in number of days;

=num( date#(floor(max(field1)),'YYYYMMDD') - date#(floor(min(field1)),'YYYYMMDD') )

Re: Difference between two dates

Tue, 17 Jul 2012 13:18:04 GMT

(dayStart(timestamp(EndDate)) - dayStart(timestamp(StartDate))) + If(Day(StartDate) = 1, 1, 0)

see the attached file