KNOWLEDGE BASE

How to Retain Sort Order After Drilling up and down on a Date Field


Published: 27 Dec 2023
Last Modified Date: 27 Dec 2023

Question

How to retain the sort order on date fields that are placed in a viz and displayed in a hierarchy?  The sort order is initialized by drilling up and down.

For example, assume that we changed the sort order of a lower level of a hierarchy of the Date field as follows:

User-added image

Drilling up and down the hierarchy will initialize the sort order. How can we maintain the sort order after drilling up and down?

User-added image User-added image

Environment

  • Tableau Desktop

Answer

Follow along in the sample packaged workbook found in the Attachments section of this article.

When Date or Date & Time fields are used in the viz, they obtain a functionality of automatic date hierarchy drill down. Since the date hierarchy fields that were automatically drilled down are not stored in the Data pane, the changes in sorting are initialized by drill-up and drill-down. To avoid this, you can manually create the hierarchy to retain the sort order.

1. Create a calculated field and name it Month of Order Date.
DATENAME('month',[Order Date])
2. Create a calculated field and name it Day of Order Date.
DAY([Order Date])
3. Right-click the Day of Order Date field in the Data pane and select Convert to Dimension.
4. Right-click the two fields above and select Hierarchy > Create Hierarchy. Name it as you like.
5. Add Month of Order Date and Day of Order Date to Rows.
6. Add SUM(Sales) to Text.
7. Follow the instructions of the documents below and change the sort setting of Day of Order Date. Then, the sort order will be retained.
 Sorting Data
 Set the default sort order for the values within a categorical field
Did this article resolve the issue?