# Chart Display - Daily Totals + Moving Average

**URL:** <https://forum.inductiveautomation.com/t/chart-display-daily-totals-moving-average/14274>\
**Category:** Ignition\
**Created:** [May 12, 2017, 12:36am UTC](https://forum.inductiveautomation.com/t/chart-display-daily-totals-moving-average/14274 "2017-05-12T00:36:02Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Paullys50](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paullys50/32/3324_2.png) [@Paullys50](https://forum.inductiveautomation.com/u/Paullys50)\
**Post date:** [May 12, 2017, 12:36am UTC](https://forum.inductiveautomation.com/t/chart-display-daily-totals-moving-average/14274/1 "2017-05-12T00:36:02Z")

</div>

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

```auto
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:

```auto
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!

---

<div class="post-metadata">

**Author:** ![Paullys50](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paullys50/32/3324_2.png) [@Paullys50](https://forum.inductiveautomation.com/u/Paullys50)\
**Post date:** [May 12, 2017, 2:54am UTC](https://forum.inductiveautomation.com/t/chart-display-daily-totals-moving-average/14274/2 "2017-05-12T02:54:12Z")

</div>

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:

```auto
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!