Creating a year-on-year comparator

To create a year-on-year comparator, you need to combine everything that we saw previously: Calculated FieldReference Line, and Parameter

Your mission (if you accept it) is to build a visualization where you can see the Sales by Sub-Categories of a selected year compared to the previous year with a Reference Line. Color also helps you to quickly spot the sub-categories where the sales are lower than the past year. Here's the final result: 

Quite impressive, isn't it? Let's build it! Follow the tutorial:

  1. Create a Parameter to choose the year. Configure it as an Integer with a List of allowable values. The list is composed of four values: 2015, 2016, 2017,  and 2018. You can, if you want to make it perfect, change the display of the values to remove the thousand separators (by modifying the display format or editing the Display As column). Name it Select a year, and choose 2018 as the current value.

Your Parameter configuration window should look like this:

  1. Display your Parameter (right-click on it and select Show Parameter Control). 
  2. Create a Calculated Field that will return the sales of the selected year. Name it Sales - Selected year and write the following formula inside: if YEAR([Order Date]) = [Select a year] then [Sales] ENDThis formula returns the Sales if the Year of Order Date is the same as the value of the Parameter. 
  3. Create a second Calculated Field, name it Sales - Previous year and write the following formula: if YEAR([Order Date]) = [Select a year]-1 then [Sales] END. This formula returns the Sales if the Year of Order Date is the value of the Parameter minus one (so if you select 2018 in the Parameter, the formula returns the Sales value of 2017).
For each Calculated Field make sure that the calculation is valid. 
  1. Build the visualization: put Sales - Selected year in ColumnsSub-Category in Rows and Sort them from highest to lowest. You can now play with the Parameter to show the Sales of the selected year. 
  1. Put Sales - Previous year in the Detail property. Your Worksheet should look as follows:

  1. Go to the Analytics pane and add a Custom Reference Line on each cell. For the value of the Reference Line, select Sales - Previous year (the aggregation doesn't matter as we are on the cell level). In the Formatting part, you can make the line a bit bigger and darker. Here's how your Reference Line should be configured:

  1. Visually, you should be able the see the difference between the selected year and the previous year, thanks to the Reference Line.
  2. To add the final touch, create a new Calculated Field, name it Is selected year better ? and write the following formula: SUM([Sales - Selected year ]) >= SUM([Sales - Previous year]). This Calculated Field is a Boolean that returns True if the sales of the selected year are higher than the sales of the previous year and False if not.
  3. Finally, put the new Calculated Field, Is selected year better ?, in Color. If you want, you can modify the Colors. In the end, your Worksheet should look as follows:

You can play with the Parameter to change the year, and you'll directly spot the problematic sub-categories. This visualization is a good exercise because it makes you practice a lot of Tableau features and it's also a great way of comparing two different years. 

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.149.236.187