Chart Display - Daily Totals + Moving Average

Ignition
1

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!

2

So I’ve got a solution. I have two custom properties on my chart:

1 - DailyTotals (dataset)
2 - MovingAvg (dataset)

DailyTotals is bound to my first query in my original post.

MovingAve is bound to this query:

SELECT Week_Day, AVG(Quantity)  OVER (ORDER BY Week_Day rows unbounded preceding) 
  FROM (
     SELECT Day(t_stamp) [Week_Day], SUM(Quantity) [Quantity]
     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))
AS SUBQUERY

I haven’t queried a result set from another query before, that was the missing link. If you have other suggestions I’d listen!

1 Like