Performing a Calculation with a Roll-Up table as denominator
Performing a Calculation with a Roll-Up table as denominator
All,
Looking for some advice / best practices on how to accomplish something that I've run into 2 or 3 times and I've had a workaround each time but its probably not the best solution. This occurs when I have a table which I have to perform division logic based on the SUM data of a log file but a single denominator value based on a join from a summary table.
For example, lets assume I'm trying to do an industrial dashboard and I have X pieces of manufacturing equipment that is available a specific amount of time each day (but varies by equipment). This equipment manufactuers widgets that take a variable amount of time to produce, and there is a column in my data set that tells me how much each widget takes to produce from a log file type format. So to Illustrate, the base data set I have is below:
Widgets Produced (LOG)
Machine Available Time Table
So to get my utilization for Machine #21 is this example, I would do the following calculation: (90+37+41) / 727 = That equipment utilization (When converted to percent).
What ultimately ends up happening is if I Merge these on Machine \ Equipment # in Si sense for filtering purposes, my data duplicates. I assume because SiSense is joining the Total Time Available from Table 2, to each row of my log file. I can fix this by doing my calculation based on a MIN(Time Available Seconds) to pull only one available time in my calculation as the denominator instead of my SUM, but that only works if I"m looking at a single machine. If I'm looking at multiple machines, this does not work.
The only work around I've found for this is to sum up data in my Available Time Table and create a new table that contains data similar to the following. This works but the downside of this is I cannot utilize any filters from my FACT table, and essentially have to create this on a sesperate dashboard.
Anyone have ideas on a better way to accomplish this type of calculation, and ideally still be able to use my fact table (from log file) filters?
-
Hi Weston,
what I would propose doing is allocation of machines total time per each entry in log. You could take machine 21 time 727, and calculate individual time allocation per log line per date. So in you case then it will be 727/3, then lookup this value back to log, and then use SUM on it to get proper total down the line from fact table alone. If you would like to discuss details of that, feel free to reach out to me at michael.becker@qbeeq.pl and I could help you sort it out.
Regards,
Michał Becker
Please sign in to leave a comment.
Comments
2 comments