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


(Hisham) #1

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.

(drake) #3

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?

(Hisham) #4

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? :smiley:

(Paul Wadsworth) #5

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?

(Hisham) #6

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:mm:ss format is not important for me, I wish i even get it in 123.45 hrs format.

(Paul Wadsworth) #7

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

(drake) #8

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!

(Hisham) #9

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 :heart_eyes::heart_eyes::heart_eyes: