Compare dates across rows

Question

My query is returning two rows which are question responses for a given person. Row 1 has 'Leaving Date', row 2 has 'Returning Date'. I need to compare the dates, so I suppose they must be on the same line for use in a formula. How to get the dates on the same row?

| Person ID | Response ID | Reason  | Leaving Date | Returning Date |  
| 23        | 38          | bye     | 1/02/132     |                |  
| 23        | 41          | hello   |              |  1/09/13       |  

Answer 1

There are three ways.

If the row with the Returning Date will always be after the row with Leaving Date, then you can use Previous():

=DaysBetween(Previous([Leaving Date]);[Returning Date])

But it would likely be better to use an In context operator to grab the appropriate value:

=If Not(IsNull([Returning Date]) Then
    DaysBetween (
        Max([Leaving Date]) In ([Person ID];[Response ID]);
        [Returning Date])
    )

Lastly, if you don't actually need the Response ID and Reason displayed, you can remove them and wrap the two Date objects with =Max(); that will cause them to aggregate to the same row. Then the comparison is easy:

=DaysBetween(Max([Leaving Date]);Max([Returning Date]))

All my examples are assuming that you're comparing the dates with DaysBetween, but of course any other comparison would be valid here.