KNOWLEDGE BASE

Comparing a Measure Based on Selected Year


Published: 21 Sep 2017
Last Modified Date: 18 Sep 2020

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?