KNOWLEDGE BASE

Comparing a Measure Based on Selected Year


Published: 21 Sep 2017
Last Modified Date: 22 Dec 2017

Question

How to compare a measure over time to a selected year. 

 

Environment

Tableau Desktop 

Answer

This example uses Superstore sample data to compare Sales by year of order date, and can be reviewed in the attached packaged workbook.

Step One: Create parameter and calculated fields
 
  1. Right click in the data window and select Create > Parameter using the following options:
    • Name : Year Parameter 
    • Data Type: Integer
    • Allowable Values: List
    • Display Format: Choose Custom and uncheck "Include thousands separators"
  2. Enter all years - in this example, 2014, 2015, 2016, and 2017, then click OK.

  • Create a calculated field [Selected Values] to select values for a particular year:

    iif(DATEPART('year', [Order Date]) = [Year Parameter],[Sales],null)

  • Create a calculated field [Chosen Year Values] to apply those values for the appropriate dates every year:

    window_max(sum([Selected Values]))

  • Create the final calculated field [Difference] to find the difference between each year and the fixed year chosen in [Year Parameter].

    SUM([Sales])-[Chosen Year Values]


    Note: if not comparing the sum of values, you can change "SUM" to the desired aggregation.

Step Two: Build the viz.
  1. Drag [Order Date] to Columns.
  2. Drag [Sales] to Rows
  3. Right click on [Year Parameter] and select Show parameter control
  4. Drag [Difference] to the view. In this example, place Difference on Label on the marks card.
  5. Right click [Difference] on the Marks card  and select Edit Table Calculation
  6. Choose Compute Using Specific dimensions > Year of Order Date



 
Did this article resolve the issue?