For our Friday challenge we were tasked with solving a Workout Wednesday Tableau challenge.

Having just learnt about table calculations and LODs, either of these had to be the topic of the challenge.

I went with the week 33 challenge. It was a bit challenging as there were quite a few moving parts involved.

Here’s a walkthrough of how I tackled the challenge using the provided Sample Superstore Data:

Make the Line Graph with Parameters and Moving Average

  • The instructions in the data request that some of the sub-categories are grouped together. So let’s start by making a group. Right click on sub-category, create a group and select the multiple sub-categories (control click to select them all).

  • While we’re at it, we’ll make a set of the sub-categories. This will enable us to have the specific sub-category lines change from grey when the sub-category is unselected to a colour for it to stand out. Right click sub-category and create a set. Select all in the general tab and press OK.

  • Drag order date onto the column shelf. Do so with a right click which allows you to select the correct date format which is continuous month.

  • Create two parameters to adjust the moving average. The challenge specifies the user needs to be able to change both the months before and months after the moving average. Based on the end result the parameter selection options range from 1-6 in intervals of one. So, adjust both parameters to match that.

  • Then we use these parameters as inputs for the moving average of sales. So we make a calculated field with the following formula and drag that onto the rows shelf:

WINDOW_AVG(SUM([Sales]), -[Moving Average Prior Months]+1, [Moving Average Post Months])

 

  • We then need to create the moving average, so right click on the calculated field you just dragged onto the rows shelf and select Edit Table Calculation. This took a bit of playing around, so feel free to use a crosstable in a new tab to help (right click on the worksheet and duplicate with a crosstab). Select specific dimensions and then make sure Month of Order Date is ticked.

  • Then drag sub-category (group), onto colour in the marks card. Measure Names onto the filters mark and onto detail in the Marks card. This will enable you to drag the sub-category set onto colour as well. Then select show set. Right click on the 2 parameters we made at the start and select show parameters. This is how we build the coloured line graphs.

  • Now it’s time to build the background area charts. It is a bit subtle but there are two area charts, one grey for the maximum rolling average values and one white for the minimum rolling average values.
  • Start by making two calculated fields. One called Min with this formula: WINDOW_MIN([“previous calculated field you made above”). And one called Max with this formula: WINDOW_MAX([“previous calculated field you made above”). These will taking the lowest and highest rolling averages for each month.
  • Drag measure values onto the rows shelf and remove all the other measures except the newly created min and max. Edit the table calculation of both of these and make sure specific dimensions is chosen with the sub-category group selected and month unselected. Then go to the analysis dropdown option at the top of the screen and un-select aggregate measures so that two individual area charts are made and not stacked.

  • Then drag measure names onto the colour option and choose a grey for the max and white for the min. We’re all good to go so create a dual axis with the line graphs and sychronize the axis.

 

 

 

 

Tim Fawcett
Author: Tim Fawcett