Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

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

  • 3 February 2019
  • 7 replies
  • 595 views
H

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.

image


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

D
Rank

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?

H

Thanks Drake for your reply,

my ultimate goal is to have a table like below:

image


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? 😃

Paul_Wadsworth
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?

H

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:

image


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.

Paul_Wadsworth
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

https://discourse.looker.com/t/how-would-i-use-the-new-duration-type-in-a-dimension-group/9573

D
Rank

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!

H

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.


image


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

image


and the final result was:


image


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

Reply


Terms of Service