Sorting of Month filter

[Zirva]

I have Month as a part of my key/link table since its in multiple tables. The Month is in text form. The issue is that when I put it in the filter pane, its not showing me months in the right order. I have a master table for Date where the Month is mapped against CalenderGMonthNumber which gives it the Ids. I have tried sorting it by expression and putting CalenderGMonthNumber in it but its still not showing the right order. Can someone please help with a solution? 

[igoralcantara · Accepted solution]

Use the Dual function combined with a mapping table.

for example:

map_months:

mapping

load * inline [
month, num

jan, 1

feb, 2

mar, 3

…

];

in your link table load:

…

dual( month, applymap(‘map_months’, month, 99) as month

[igoralcantara]

Expanding on my example, Dual creates a field with both: text and numeric values. Now in the chart you simply sort by numerically and remove the alphabetically option.