# How to calculate difference between dates in same column?

I want to calculate difference in days between dates located in the same column. This difference would be calculate between one date and the previous date.

Dates are not ordered. I tried the following:

DateDiff("day",Max([Date]) OVER (Previous([Date])),[Date])

and

DateDiff("day",first([Date]) OVER BinByDateTime([Date],"Year.Quarter.Month.DayOfMonth",3),[Date])

Unfortunately, both of these methods do not work and the second one gives me an error message ("Year function not found").

Could you please help?

### (2) Answers

Your first expression is close, but the [Date] column in the OVER expression will be navigated in the natural date order, not in any particular data order (either how it was imported or sorted). Therefore you need to have a separate column in the 2nd part of the OVER function that has the correct order that the OVER navigation can use. For example, if you had a column with your RowId() named [RowId] you could use:

- DateDiff("day",Max([Date]) OVER (Previous([RowId])),[Date])

or - DateDiff("day",Max([Date]) OVER Intersect([myGroups],Previous([RowId])),[Date])

or on a visualization axis like a Cross Table - DateDiff("day",Max([Date]) OVER (Previous([Axis.Rows])),[Date])

And your second try, the scope of the OVER expression does not allow you to use functions like Year() for the navigation, so you need to just use a column there.

## 4 Comments

If your dates are not in order you will have to use Rank() instead of RowId(). But I am confused by your data set and calculations. If I take the Groups, Start_End_Date, and Date columns from your xlsx, and add 2 columns like this:

```
1. Select File > Add Data Tables...
Source: Data loaded from clipboard.
Worksheet: Sheet1
Last reload: 6/5/2017 11:32 AM
2. Insert > Calculated Column...
Column name: Rank
Expression: Rank([Date])
3. Insert > Calculated Column...
Column name: DateDiff_based_on_rank
Expression: DateDiff("day",first([Date]) OVER (Previous([Rank])),[Date])
```

Then I get the results as expected (sorted):

```
Groups Start_End_Date Date Rank DateDiff_based_on_rank
Group 1 EDAT 1/1/2015 1
Group 1 EDAT 5/12/2015 2 131
Group 2 SDAT 6/23/2015 3 42
Group 2 EDAT 6/24/2015 4 1
Group 2 SDAT 7/30/2015 5 36
Group 1 EDAT 8/23/2015 6 24
Group 2 EDAT 8/24/2015 7 1
Group 1 SDAT
Group 1 SDAT
Group 1 SDAT
Group 2 SDAT
Group 2 EDAT
```

Are you sure you created your [DateDiff_based_on_Rank] correctly? and are not comparing RowID or something?

Thank you for your answer. Do you suggest to do the following then:

Rank([Date])

DateDiff("day", first([Date]) OVER Previous ([Rank]), [Date])

Rank here is the calculated column. This expression unfortunately gives me only values of 1 and 2 as difference in dates which is not correct.

If I use instead the following:

RowID()

DateDiff("day", first([Date]) OVER Previous ([RowID]), [Date])

RowID here is the calculated column, then this calculates the date difference correctly. However, the problem is only that the dates are unfortunately not always in the correct (chronological) order.

Also, there are a lot of dates missing.

I attached an example in Excel.

Would there be a way to calculate the difference in days between the chronologically ordered dates?

You can use Rank() to generate your own index based on a sorting of any columns, and then use that in your OVER function. What order do you want to use? What exactly 'doesn't work' about your first expression? Do you have a sample dxp/data set?

Hi Sean, Thank you for your answer. Unfortunately the dates are not ordered correctly in the original source. Therefore even adding the RowID column will not help as that will also represent the wrong order. Would there be a solution to this, for example sorting dates in Spotfire and performing calculations of difference in dates on sorted data?

Thank you for your answer. Do you suggest to do the following then:

Rank([Date])

DateDiff("day", first([Date]) OVER Previous ([Rank]), [Date])

Rank here is the calculated column. This expression unfortunately gives me only values of 1 and 2 as difference in dates which is not correct.

If I use instead the following:

RowID()

DateDiff("day", first([Date]) OVER Previous ([RowID]), [Date])

RowID here is the calculated column, then this calculates the date difference correctly. However, the problem is only that the dates are unfortunately not always in the correct (chronological) order.

Also, there are a lot of dates missing.

I attached an example in Excel.

Would there be a way to calculate the difference in days between the chronologically ordered dates?

Attachment | Size |
---|---|

example.xlsx | 10.66 KB |

## 0 Comments

+ Add a Comment### Similar Questions

## Haven't found what you are looking for?

## Existing Best Answer

This Question already has a 'Best Answer'. If you believe this answer is better, you must first uncheck the current **Best Answer**