Drill Down Aggregation Issue

RTM

We are using Domo to visualize our manufacturing data. There are planned hrs based on the number of parts and the amount of time it should take to manufacture those parts. Then there are actual hrs, which is how long it actual takes to manufacture those parts. Efficiency is calculated by dividing the planned hrs by the Actual hrs. There is downtime daily which causes a loss in efficiency. Need to show how much impact that downtime is having to our efficiency. Need to be able to drill down on that loss from a Team→Station→Issue→Shift. For each level of calculation the plan hrs and act hrs needs to be stay the same. Due to downtime varying from team to team, day to day and shift to shift struggling to keep the plan hrs and act hrs consistent through all levels of the drill down.

Domo Sample.xlsx

This is what a dataset might look like over a few days. What is the most efficient way to aggregate the act hrs and planned hrs so they remain the same as you drill down?

marcel_luthi

Based on your description, it'd seem that instead of a LEFT JOIN, you should have used a simple APPEND ROWS in your ETL, this would allow you to have a single entry for everything, as long as your drill down only leverage the fields both tables have in common (date, team, shift), and from there you could see which stations presented issues and the like, this is since the first table does not possesses the granularity at the Station level, so you cannot know how many hrs each station was scheduled for and how many it actually worked. Hope this makes sense.