KNOWLEDGE BASE

Sort Records Based on This Year's and Previous Year's Sales


Published: 25 Oct 2022
Last Modified Date: 25 Oct 2022

Question

How to sort records based on this year's and previous year's sales.

If you want to sort the records based on this year's sales, or if there are no sales this year, based on the previous year's sales.

Current behavior
User-added image
Desired behavior

User-added image

Environment

  • Tableau Desktop

Answer

1. Connect to attached data source 'deta.xlsx' from Tableau Desktop.
2. Drag Date to Columns and Company to Rows.
3. Drag Sales to the view as show below.
User-added image
4. Create a new calculated field as follows:
     Name : Previous Year Sales
     Calculation : {FIXED [Company]:SUM( 
                           IF DATEPART('year', [Date]) = YEAR(TODAY()) - 1
                           THEN [Sales] END)}
5. Create a new calculated field as follows:
     Name : This Year Sales
     Calculation : {FIXED [Company]:SUM( 
                           IF DATEPART('year', [Date]) = YEAR(TODAY())
                           THEN [Sales] END)}
6. Create a new calculated field as follows:
     Name : Sort by Sales
     Calculation : IF [This Year Sales] >= 1
                           THEN [This Year Sales] * 1000000000000
                           ELSE  [Previous Year Sales]
                           END
7. Right-click on Company on Rows and select Sort.
User-added image
8. Set the sort as follows:
User-added image
 
Did this article resolve the issue?