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.

marcel_luthi

What do you mean by them remaining the same? From your sample data, the planned hours on 7/10/2023 for Team A are: 40, but for 7/10/2023 are only 8, and if you look at team B you have 7/10 = 17 vs 7/11 = 42.5 if you then drill down to an specific Station, each have a different number, so not sure what you want to have remain the same. In general if you need global totals you can do so with the Fixed Function, but I'm not fully understanding the use case.

RTM

Let me try and provide some more clarity. The dataset I showed is after it goes through ETL.

There are two datasets feeding the ETL. The first is one that contains date, team, act hrs, plan hrs and shift.

The second contains date, team, shift, station, issue and DT.

I used a left join on date, team and shift. When I do that, act hrs and plan hrs duplicate for every occurrence of DT, which is one issue. The bigger issue is no actual hrs show up if there is no downtime. So when I do calculations those hrs are missing skewing the data. My denominator (act hrs) should always be the total act hrs worked regardless of whether there is downtime or not for a particular issue or on a particular station. If I pull out the actual hrs from the dataset this is what it looks like (below). I need to be able to see the act hrs for each and every level of the drill down Team→Station→Issue. I tried using the fixed function but it didn't work due to the gaps in the data. Not sure if this helps with the understanding or not.

		Team A	Team B	Team C	Team D
Days	Days	8.75	8.7	8.30
Aft	Aft	9.1		8.60
Days	Days	8.4	9		7.7
Aft	Aft		9.3	8.2	7.9
Days	Days	8.3	8.5	8.2
Aft	Aft	8.4		8.2	8

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.