Hello, looking for some assistance on how to approach some data analytics. Real simple, I have a machine that outputs a container, each container has a number of widgets. Every time a container of widgets exits the machine, I create a MS SQL record of the number of widgets in the container.
I have 2 tasks to accomplish. 1 - I’d like to create a chart that trends monthly daily totals of widgets produced, 2 - overlay daily moving average to this total.
I can use the following SQL binding to the chart’s data property and produce the daily totals and trend them, note I’ve hard coded the month of May to 5, and machine number to 2. So I’ve accomplished task #1
SELECT Day(t_stamp) [Week_Day], SUM(Quantity) [Total] FROM Widget_Tracking WHERE Month(t_stamp) = 5 AND (SELECT DATEPART(yy, GETDATE())) = YEAR(t_stamp) AND Machine_Number = 2 GROUP BY Month(t_stamp), DAY(t_stamp) ORDER BY 1
This returns a numeric day of the month and total widgets for the day, a pretty basic dataset.
I don’t have a good approach to Task #2, applying a daily moving average. As it stands the production rates can vary so a daily total trend can be a bit noisy.
I’ve found that if I take the above query, and insert the results into a new table, I can apply the following query to return a moving average:
SELECT Week_Day, AVG(Quantity) OVER (ORDER BY Week_Day rows unbounded preceding) FROM daily_totals
It will produce a moving average dataset where:
Day 1 Average = Day 1 Total/1
Day 2 Average = (Day 1 Total + Day 2 Total)/2
Day 3 Average = (Day 1 Total + Day 2 Total + Day 3 Total)/3
So the question is, how to best approach this. Can I do something directly on the Chart component to achieve my moving average trend line? Or do I need to leave it up to my SQL queries and find a way to merge my two queries listed above? I have multiple machines and I’ll show this data for each month, I’m not sure the creation of all the tables to run my average query against is a very “clean” solution. I feel I’m over thinking this one, but since this isn’t time-series data I’m not sure I can apply and Easy Chart and calculated pens.
Hoping for some suggestions!