3 Replies Latest reply on Jul 11, 2018 1:43 PM by Michel Caissie

# Display previous date based on another date dimension

Hope this is an easy one. I have a data set that I am trying to display the first date that meets certain criteria based on the date from another dimension. My data is confidential but I have attached a workbook with a sample date set.

What I am looking for - Display the closest Install Date that is before the Departure Date for each ship.

what I would like to see- based on following criteria. Install date must be before Departure date. Display only one install date that is closest to the departure date.

Correct results are highlighted.

Seems simple, but I am having issues any help would be greatly appreciated

• ###### 1. Re: Display previous date based on another date dimension

Ty,

You can compute the closest Install Date with

{FIXED [Ship]: MIN(if [dateDiff (min)] =  [dateDiff] then [Install date] end)}

where [dateDiff] is

[Departure Date] - [Install date]

and  [dateDiff (min)] is

{FIXED [Ship] : MIN( if [dateDiff] = 0 then null else [dateDiff] end )}

Michel

• ###### 2. Re: Display previous date based on another date dimension

Thanks for your help and that worked but......

I noticed that some of my ships have multiple departure dates which brings back the incorrect data. Below is a screenshot. Ship 2 is the one to pay attention to as it has a 2 departure dates and the highlighted data is what I would want to see. so for Dep Date 2014 the Install date should be 2012 and for Dep Date 2015 it should be 2014.

I have attached a new workbook with the revised data

Thanks,

Ty

• ###### 3. Re: Display previous date based on another date dimension

The only thing you have to do is to add the  Departure Dimension in the dimensionality expression of the lod calculation.

This way the computation will be done for every  Ship-DepartureDate  group  instead of every  Ship group.

So you would have;

{FIXED [Ship] , [Departure Date]: MIN(if [dateDiff (min)] =  [dateDiff] then [Install date] end)}

{FIXED [Ship], [Departure Date] : MIN( if [dateDiff] = 0 then null else [dateDiff] end )}