Distribute Total duration between start date and end date into Hrs/ day

  • 3 February 2019
  • 7 replies

Hi, I need your help in this, I need to distribute the Total duration of work orders between start date and end date so I can calculate the Total Hrs/Day for ALL work-orders within a given month.

I managed to calculate the portion of duration that happened in the First Day of the Work Order using the below Table Calculation:

if(${max_mwo_task_end}< (add_days(1,${1st_day_date})),diff_minutes(${min_mwo_task_start},${max_mwo_task_end})/60,diff_minutes(${min_mwo_task_start},add_days(1,${1st_day_date}))/60)

And the portion of duration that took place in the Last Day of the Work Order like below:

coalesce(if(${last_day_date} = ${1st_day_date},0, (diff_minutes(${last_day_date},${max_mwo_task_end})/60)),24)

So , each day_of_month in between the Start and the End dates of the Work-orders shall have 24 hours.

I need to represent each day of the month with the Sum of duration within that day.

7 replies

This is actually a really complex problem and I can’t think of a way to solve this by using LookML or table calculations. It sounds like your desired output would be something like this:

Date | SUM(Downtime)

2018-05-02 | 73.75hrs

2018-05-01 | 48.25hrs

In which case, I’ve solved a similar problem in the past with this approach:

  1. Create a date table

  2. You’ll then need to find a way to JOIN your existing data to the date table. I’ve used cross joins in the past.

  3. You’ll then aggregate (group by) your dates and find a way to SUM all of the downtime. Here, you’ll want to nest a CASE statement within your SUM so that you’re accurately capturing the downtime. Something like:

SUM( CASE WHEN [date] <= [start of downtime] THEN diff_minutes( [date], [end of downtime] )

Does that make sense?

Thanks Drake for your reply,

my ultimate goal is to have a table like below:

Each day should SUM all the Downtime portion - of each work-orders - that falls in it.

I’ve done this in Excel via 3 table , First one calculates the portion of Downtime within the first day for each Work-order , Second one calculates the portion of Downtime within the Last day for each Work-order , and the third table adds 24 hrs for each day in between the start and the end of every Work-order. Then I added ALL up into the above screenshot.

is there any way to pursue smarter approach in Looker? 😃

Userlevel 2

I tried to do something similar and the problem I had was there is no [h]:MM:SS format, so I got a time, but couldn’t display it like your 369:25:26 example, IIRC I found some way to display it as “369 hours” I think, but I wanted the complete time.

I’m just curious though, if you have two dates, can’t you just do the difference between the two? Do you need to work out the first/last days separately and add the days between?

Hi Paul, I don’t just need the Duration between Two Dates.

I need to split the “Portion” of each Work-order duration for Each month day.

like below:

then all the Downtimes of Certain day will be SUM like the table I posted above., So i can keep track of the TOTAL DOWNTIME per day.

P.S. the hh🇲🇲ss format is not important for me, I wish i even get it in 123.45 hrs format.

Userlevel 2

This is where I got the information to get as far as I did, unfortunately I removed everything since I couldn’t get that [h]:MM:SS format I needed

Hey Hisham, there is a way to do this but it requires using a date table and joining all of your data onto that “base” table.

Try the method I outlined above – create a date table, cross join your data, and then you’ll be able to apply logic within a SUM function so that you can calculate the total downtime on any given day (like the example you provided).

Let me know if this works!

Dear Drake, your proposal of using date table is genius. thanks to the help of a colleague from Looker team (David Szajngarten) who walked me through the rest of the exercise.

the trick was in building the relation of the model like below:

and the final result was:

thanks alot for both of you for the great help 😍😍😍