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

Control Limits on Graphs - subset of dates

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

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

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