4 Replies Latest reply on Nov 2, 2018 3:27 AM by Vertika Singh

# DATEDIFF help

I have a workbook where I want to calculate the DATEDIFF of 2 dates from 2 separate records. My data goes a little like this:

Col 1: ID

Col 2: Current Status

Col 3: Updated Status

Col 4: Modified Time

There are further columns of data within the data source which is why I haven't included the workbook. An example of some of the records go:

IDCurrent StatusUpdated StatusModified date
123Status AStatus A01/01/2019
123Status BStatus B05/01/2019
123Status CStatus C07/01/2019

For this example I want to calculate the difference in days for ID 123  between Current Status = Status A and Updated Status = Status B.

I just can't figure out how to do the Date diff on values in other dimensions. Any help is appreciated.

Thanks,

Gareth

• ###### 1. Re: DATEDIFF help

Gareth,

You can do this with LOD:

{fixed [ID] : max(if [Current Status]='Status B' then [Modified date] end)-max(if [Current Status]='Status A' then [Modified date] end)}

Thanks,

AB

1 of 1 people found this helpful
• ###### 2. Re: DATEDIFF help

Hi Gareth,

To compare your Status Dates, you have to put them on the same line, here is a way to do it:

1. Create a calculated dimension following this exemple:

IF  [Status] = 'Status A'

then { FIXED [ID] : MIN( IF  [Update] = 'Status B' THEN [Date] end ) }

end

I replied to the example you gave us, but if there are other functional logic behind, you have to fulfill the calculation with other cases.

2. Create the calculation with the DattDiff function.

Regards,

Cédric

1 of 1 people found this helpful
• ###### 3. Re: DATEDIFF help

Hi,

Try this,

DATEDIFF

('day',

{FIXED [ID]:MIN(IIF([Current Status]="Status A",[Modified Date],NULL))},

{FIXED [ID]:MIN(IIF([Updated Status]="Status B",[Modified Date],NULL))}

)

Let us know if this help.

Mahfooj

1 of 1 people found this helpful
• ###### 4. Re: DATEDIFF help

IF [Updated Status]='Status B' THEN

DATEDIFF('day',

{fixed [ID] : min(if [Updated Status]='Status A' then [Date] END)},

{fixed [ID] : min(if [Updated Status]='Status B' then [Date] end)})

elseif [Updated Status]='Status C' THEN

DATEDIFF('day',

{fixed [ID] : min(if [Updated Status]='Status B' then [Date] END)},

{fixed [ID] : min(if [Updated Status]='Status C' then [Date] end)}) END