KNOWLEDGE BASE

Sort A Combination Of Start Dates And End Dates


Published: 31 Jul 2020
Last Modified Date: 31 Jul 2020

Question

How to sort a combination of start dates and end dates

Environment

  • Tableau Desktop

Answer

1. Combine the start and end dates by a calculation like below

Using DATEPART function for output that returns part of date as string

STR(DATEPART('day',[Start date])) +  "-" +  LEFT(DATENAME('month',[Start date]),3) + "-" + right(STR(DATEPART('year',[Start date])),2)
+
"-" +
STR(DATEPART('day',[End date])) +  "-" +  LEFT(DATENAME('month',[End date]),3) + "-" + right(STR(DATEPART('year',[End date])),2)


OR

Using DATENAME function for output that returns part of date as integer

STR(DATEPART('day',[Start date])) +  "-" +  STR(DATEPART('month',[Start date])) + "-" + STR(DATEPART('year',[Start date]))
+
"-" +
STR(DATEPART('day',[End date])) +  "-" +  STR(DATEPART('month',[End date])) + "-" + STR(DATEPART('year',[End date]))


2. Use Start date in the view with Exact Date as level of detail 
3. Sort by ascending or descending order on Start date
4. Right-Click the Start date dimension and deselect Show Header
 

Additional Information

Directly sorting on combined dates field as a String will result in incorrect results
Did this article resolve the issue?