Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Difference between two dates

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!

11 Replies
m_woolf
Master II
Master II

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)

Not applicable
Author

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?

m_woolf
Master II
Master II

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

Not applicable
Author

Put the date into two fields date1 and date2

and use mwoolf's formula to calcualte the difference

floor(date1) - floor(date2)

m_woolf
Master II
Master II

Are you trying to do something like my attachment?

Not applicable
Author

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

m_woolf
Master II
Master II

Here is a screenshot:

ToniKautto
Employee
Employee

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';

Not applicable
Author

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.