6 Replies Latest reply on Sep 26, 2018 5:38 AM by Shinichiro Murakami

# Calculate downtime between end and start date

Hi,

The structure of the data source is : one record for each reservation (ID reservation).

I want to calculate the downtime for each car (Twizy) between the end date of the last reservation (which is on one row) and start date of the next reservation, which, for the same car, is on another row.

I guess a Level-of-detail calc at the car (Twizy) level will do?

Final goal : average downtime for all cars, rankings of downtime per car, total downtime etc.

Many thanks in advance.

Numa • ###### 1. Re: Calculate downtime between end and start date

Hi,

You may try this,

It'll return the downtime for each car in minutes.

{FIXED [Twizy]: AVG(DATEDIFF('minute',{FIXED [Twizy]:MIN([Start Date])},{FIXED [Twizy]:MAX([End Date])}))}

Based on the above logic you can get the rank,total

Let us know if this help.

Mahfooj

• ###### 2. Re: Calculate downtime between end and start date

I notice you have more than 2 Twizy records. For example 373 PE occurs thrice in the screenshot. In this case, LODs might not work except there is another field which can be used to group the Twizys in pairs. What you probably need is a table calculation. Can you post some sample dummy dataset?

• ###### 3. Re: Calculate downtime between end and start date

@Okechuku: see attached.

• ###### 4. Re: Calculate downtime between end and start date
1 of 1 people found this helpful
• ###### 5. Re: Calculate downtime between end and start date

Great work Shin! Wow. Many thanks.

Wondering what might render the calc less costly in terms of CPU, beside doing it outside of tableau...

• ###### 6. Re: Calculate downtime between end and start date

As a nature of table calc, if the data size is big, it requires lots of resources.

And Tableau prep does not support across row calc yet.

I am saying other "real" ETL tool is wanted if you have lots of data.

BTW, if this is OK, could you please mark my answer as correct to close th thread.

Not from inbox but from original post. Thanks,

Shin