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

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

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] )
ELSE NULL
END )
``````

Does that make sense?

(Hisham) #4

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?

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.

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
https://discourse.looker.com/t/how-would-i-use-the-new-duration-type-in-a-dimension-group/9573

(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