2 Replies Latest reply on Feb 23, 2016 6:42 AM by Nina Schaub

    Control Limits on Graphs - subset of dates

    Nina Schaub

      I am attempting to recreate the graph that I have attached an image of.

       

      It is a look at 26 weeks of data but the average, LCL and UCL are per 13 weeks, rather than the whole 26 weeks. Any insights on how I might accomplish this in Tableau? I know how to use the reference lines for average and based on standard deviations, but can't figure out how to do it on the subsets of dates.

      Thanks

        • 1. Re: Control Limits on Graphs - subset of dates
          Steve Mayer

          Nina - I posted the attached solution using the Superstore sample data. It primarily uses table calculations to solve the problem. I also use a level-of-detail calculation to be able to partition weeks into 13-week periods for the table calculations. I'll provide a high level overview of the solution and you can dig into the workbook to understand the details.

           

          I created a LOD calculation that returns the difference in weeks from the maximum date in the data set. Depending on how you partition your 26-week periods, you may be able to do this with simple DATE functions. I decided to use an LOD because I'm using the Superstore [Order Date] in my example, and I wanted a 26 week period back from the MAX date in the data set:

           

          [Weeks from Max]

          DATEDIFF('week', [Order Date], { MAX([Order Date]) } )

           

          I created another calculated field that puts every record into one 13-week period (0 for the latest 13-week, 1 for the next, etc):

           

          [Period]

          FLOOR([Weeks from Max] / 13)

           

          I created three new Measures for calculating 13-week LCL, UCL and Average:

           

          WINDOW_AVG(SUM([Sales]))

          WINDOW_AVG(SUM([Sales])) - WINDOW_STDEV(SUM([Sales]))

          WINDOW_AVG(SUM([Sales])) + WINDOW_STDEV(SUM([Sales]))

           

          The LCL and UCL are one SD from the mean. If you need 2 SDs from the mean, simply multiple the WINDOW(STDEV) by 2 in the above calcs.

           

          Finally, set up your Viz like so, making sure you add [Period] to the level of detail of the LCL/UCL chart. I used a dual axis chart, so that the main chart didn't break for each 13-week period.

           

           

          Hope this works for you,

           

          -Steve

          • 2. Re: Control Limits on Graphs - subset of dates
            Nina Schaub

            Thanks Steve, this worked like a charm! Appreciate the help!