How to sort a fiscal date column

[ameliahelland]

Does your data follow a fiscal year calendar? If so, you may have noticed that properly sorting your date column isn’t as easy as setting a simple ascending/descending order (because date columns are ranked according to a classic calendar, and text columns containing date information are ranked alphanumerically). That said, it’s not too difficult to properly sort a column of fiscal dates in Sigma!

We can do it in four steps starting with text data that looks like this:

1. Add a new column called “Date” in the same level as “Fiscal Year Quarter Month”.
   Use the following formula:
   DateTrunc("day", DateParse(Concat(RegexpExtract([Fiscal Year Quarter Month], "(\\d+)", 1), "/", RegexpExtract([Fiscal Year Quarter Month], "(\\d+)", 3), "/", Text(1)), "%y/%m/%d"))
   This essentially transforms the text values into date values.
2. Add a new column called “Month Order” (in the same column level as before).
   Use the following formula:
   Switch(Month([Date]), 11, 1, 12, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 7, 6, 8, 7, 9, 8, 10, 9, 11, 10, 12)
   This assigns the appropriate rank values for each month.
3. Add a new column called “Year Order” (again, in the same column level).
   Use the following formula:
   Year([Date])
   This extracts the year number from the date column.
4. Feel free to hide the helper columns from steps 1 through 3. Then, right-click the “Fiscal Year Quarter Month” column header and select the gear icon at the top of the menu (to the right of the ascending / descending sort options). Enter the following columns (in the same order) and click “Apply”:
   
   

Voila! Now you’ve got your Fiscal Year Quarter Month column in the right order.