2 Replies Latest reply on Jul 24, 2017 7:45 PM by Anthony Erickson

    Prior Month Control Chart with MTD Bar Graph

    Anthony Erickson

      Hello Everyone,

       

      Hopefully someone has come across this problem. I have two columns, date and number of records, and I would like to create a control chart based on the prior month but with a current MTD bar graph. The number of days in the prior month is not calendar days but based on the date records.

       

      Example:

       

      Date   Number of Records

      6.15   500

      6.16   200

      6.17   250

      7.01   400

      7.02   450

      x.xx   xxxx

      x.xx   xxxx

       

      For instance, xx dates in June would be used to create upper and lower control limits. Right now there are xx days in July (and every day they would get added until the end of the month and compared against the prior month's control limits). If the MTD counts go over/under the control limits, they are colored.

       

      The control chart blogs I've seen use simple and moving average control charts. These are straightforward to build and color and work well with 30 days of data. I haven't seen anything which looks at using a prior month control chart with a MTD bar chart. The process should be fully automated and would probably use calculated fields but I'm not sure on how to proceed with it (using Today and DateDiff?). I could use parameters but would prefer that it's a fully hands off process.

       

      I'll try to add a workbook in a subsequent post but the problem is easy to replicate per the explanation above.

       

      Any suggestions would be greatly appreciated!