KNOWLEDGE BASE

## Creating a Year Over Year YTD Comparison

Published: 30 Jul 2013

### Question

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

### Environment

Tableau Desktop

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.