KNOWLEDGE BASE

How to Sort the Data Dynamically by the Current Month's Ratio When a Workbook is Opened


Published: 09 Dec 2022
Last Modified Date: 12 Dec 2022

Question

When a workbook is opened, is it possible to sort the data dynamically by the current month's ratio?

Environment

  • Tableau Desktop

Answer

Below is an example using the Sample - Superstore. If you open the workbook on 2022/12/01, it will be sorted in descending order of Profit Ratio on 2022/12.
User-added image

1. Add Year(Order Date) to Columns.
2. Add Month(Order Date) to Columns.
3. Add Sub-Category to Rows.
4. Double-click Sales, Profit and Profit Ratio. Change the order in Measure Values Shelf as you need.
5. Right-click Profit Ratio field in Measure Values Shelf and change Number Format to percentage.
6. Move Measure Names field from Rows to Columns.
7. Create a calculated field and name it Fixed Profit.
{ FIXED DATEPART('year', [Order Date]),DATEPART('month', [Order Date]),[Category],[Sub-Category] : SUM([Profit]) }
8. Create a calculated field and name it Fixed Sales.
{ FIXED DATEPART('year', [Order Date]),DATEPART('month', [Order Date]),[Category],[Sub-Category] : SUM([Sales]) }
9. Create a calculated field and name it Sort.
IF DATETRUNC('month', [Order Date]) = DATETRUNC('month', TODAY())
    THEN [Fixed Profit]/[Fixed Sales]
END
10. Right-click Sub-Category in Rows and select select Sort.
11. Edit Sort as follows:
 User-added image
 
Did this article resolve the issue?