KNOWLEDGE BASE

Creating a Year Over Year YTD Comparison


Published: 30 Jul 2013
Last Modified Date: 02 Feb 2017

Question

How to create a year-over-year YTD comparison.

Environment

Tableau Desktop

Answer

These examples are available in the following blog post related to LOD  Expressions: https://www.tableau.com/about/blog/LOD-expressions and in the videos at the bottom of this page. 

Cohort Analysis example (Raw)

Based on the customer names of the Sample - Superstore data source, we want to know when customers where acquired, next to all orders they made. 
Create a calculated field to show the acquisition:
  • Select Analysis > Create Calculated Field...
  • Create a Calculated Field called "Acquisition Date" using the following formula:
    {FIXED [Customer Name]: MIN([Order Date])}
Build the view: 
  • In the following order, drag Customer Name, Order Date (choose Day and Discrete) and the Acquisition Date calculated field. 
  • Notice that the acquisition date remains no matter how many orders were made over the years. 

Cohort Analysis example (Adds up to 100%)

These steps will show the Sales based on the acquisition date of the customer. 
  • Drag Order Date to Columns (choose Year)
  • Drag Aquisition Date to Color and adjust the colors as desired per year.
  • Drag Sales to Rows.
  • Right-click SUM(Sales) and choose Add Quick Table Calculation
    • In the Calculation Type, select "Percent of Total"
    • Compute using, select Specific Dimensions and choose "Year of Acquisition Date"
    • In the Show Me menu, choose the Stacked Bar template.

Week Comparison of Profit based on Previous Year

In this example, we want to view the sales difference of the exact same week on the previous year. 

Create several calculated fields

Create a calculated field to restrict the data to the maximum date of orders made in the data source. 
  • Select Analysis > Create Calculated Field...
  • Create a Calculated Field called "Max Order Date" using the following formula:
    {MAX([Order Date])}
Create a calculated field to show the day of the maximum order date.
  • Select Analysis > Create Calculated Field...
  • Create a Calculated Field called "Day of Year Max Date" using the following formula:
    DATEPART('dayofyear', [Max Order Date])
Create a calculated field to show the numbered day of the year in a calendar year.
  • Select Analysis > Create Calculated Field...
  • Create a Calculated Field called "Day of Year of Order Date" using the following formula:
    DATEPART('dayofyear', [Order Date])
Create a calculated field show the previous year information. 
  • Select Analysis > Create Calculated Field...
  • Create a Calculated Field called "Previous Year" using the following formula:
    {min(year([Max Order Date]))}- 1
Create a calculated field show the previous year information. 
  • Select Analysis > Create Calculated Field...
  • Create a Calculated Field called "Date Filter" using the following formula:
    [Day of Year of Order Date]<=[Day of Year Max Date]

Build the view

  • Drag Order Date to the Filter card and select the years 2015 and 2016.
  • Drag "Date Filter" to the Filter card, select True and click OK.
  • Right-click and drag Order Date to Columns and choose WEEK(Order Date) (as a discreete measure). 
  • Right-click on WEEK(Order Date) and select Continuous.
  • Drag Profit twice to Rows.
  • Control and drag a copy of the filtered YEAR(Order Date) to the Color of the first Profit Mark card. 
  • Click the first SUM(Profit) Card and drag Max Order Date and Previous Year to Detail.
  • Right-click the first Profit measure on Rows, and select Add Quick Table Calculation.
    • Primary Calculation Type: Running Total and Sum.
    • Compute Using: Table (across).
  • Click Add a secondary calculation.
    • Secondary Calculation Type: Difference From.
    • Compute Using: Specific Dimension > Year of Order Date.
    • Drag this selection to the top of the specific dimensions list.
  • Right-click the second Profit measure on Rows, and select Add Quick Table Calculation.
    • Primary Calculation Type: Running Total and Sum.
    • Compute Using: Table (across).
  • ​(If applicable) Select prefered option if there are null values in the view.

Additional Information

To view the Cohort Analysis steps in action, see the video below: 


To view the Week Comparison steps in action, see the video below: 
Did this article resolve the issue?