6 Replies Latest reply on Mar 10, 2020 5:59 AM by Gopal Desai

# Datediff by subtracting 2 Rows

Hello everyone,

I would like calculate Date & time difference from 2 different Rows. I have 6 Stations(Column1). I would like calculate datetime difference between 1st station and last station. For Example value 4B6B831A, It should show me time difference between Arbeitsplatz1(1st Station) and Arbeitsplatz19(last Station). Here it should be 04.03.2020 13:54:38 - 04.03.2020 13:49:29 = 00:04:51. This time difference should show in 4th column in row

Station                Timestamp                   wERT-1          Durchlaufzeit

Arbeitsplatz19    04.03.2020 13.54:38    4B6B831A      00:04:51.

I am trying to solve this thing with lookup and Table Calculation. But it's not Happening. If anyone could you please help me out. I am attaching my workbook.

Thank you.

• ###### 1. Re: Datediff by subtracting 2 Rows

You've attached a regular workbook (twb) which doesn't include data. Any chance you can save as a packaged workbook (twbx) then re-attach? A twbx includes both the workbook and the data.

1 of 1 people found this helpful
• ###### 2. Re: Datediff by subtracting 2 Rows

Hello Gopal.

You should be able to accomplish this with LOD Calculations. I attached an example using sample superstore. Use this calculation. If you are using any filters, make sure to add them to context.

DATEDIFF('day',

{ FIXED [wERT-1 ]: min([Timestamp])}

,

{ FIXED [wERT-1 ]: max([Timestamp])}

)

Hope this helps! See Attached.

Robert Breen

1 of 1 people found this helpful
• ###### 3. Re: Datediff by subtracting 2 Rows

Thank you so much for the Reply, I attached .twbx file.

Regards,

Gopal

• ###### 4. Re: Datediff by subtracting 2 Rows

Hi Robert Breen,

Thank you so much for the Reply. If i use this calculation it shows me error Level of detail expressions cannot contain table calculation or the ATTR function.

Regards,

Gopal

1 of 1 people found this helpful
• ###### 5. Re: Datediff by subtracting 2 Rows

Hello Gopal,

any time you use a lookup() function, you cannot use that field in an LOD. I think this is what you are looking for.

DATEDIFF('minute',

{ FIXED [Wert]: min([Timestamp])}

{ FIXED [Wert]: max([Timestamp])}

)

Hope this helps! See Attached.

Robert Breen

1 of 1 people found this helpful
• ###### 6. Re: Datediff by subtracting 2 Rows

Hello Robert Breen,

i am looking for the Solution as you mentioned before.

DATEDIFF('minute',

{FIXED[wERT-1]: min([Timestamp])}

{FIXED[wERT-1]: max([Timestamp])}

)

I would like to calculate Date difference on the basis of wERT-1 not Wert. I already calculate  difference on the basis of Wert (in Bearbeitungszeit calculation field).

Regards,

Gopal Desai

1 of 1 people found this helpful