Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
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?
How will you know which two dates you want to subtract?
Put the date into two fields date1 and date2
and use mwoolf's formula to calcualte the difference
floor(date1) - floor(date2)
Are you trying to do something like my attachment?
Sorry, but I can't open your attachment. Could you write your example here?
Here is a screenshot:
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';
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.