How to sort date in MMM/YYYY format in Proper Order

[vipul_narang]

Hello,

I am currently working on a dashboard which has couple of line charts to display data over months. The x-axis shows the dimension in MMM/YYYY format (Aug/2019, for example). When i am trying to sort it by expression = Year, it is not sorting is properly (See file attached).

How can i fix this issue.

Thanks in advance!

[astoulov]

Hi, 

What data type is your month dimension in the data model? Date or string?

[astoulov]

In case you have time dimension in Date format you can create master item MonthYear:

[vipul_narang]

Thanks for a quick response Astoulov.

The data type for Month field is date type. 

I am extracting the MMM/YYYY field on the script level itself using following.

Month(Date(date#(left(dim_date, 8), 'YYYYMMDD'))) & '/' & Year(Date(date#(left(dim_date, 8), 'YYYYMMDD'))) as MonthYearCampDate,

[sunny_talwar]

Right now what you have is being extracted as text, try this instead

Date(Date#(Left(dim_date, 8), 'YYYYMMDD'), 'MMM/YYYY') as MonthYearCampDate,

Now the above is a dual field, it has a numeric value with text representation in the form of Jan/2019. Now MonthYearCampDate can be sorted numerically.

[vipul_narang]

Thanks Sunny.

I tried this but it changed the line chart. any suggestions how to fix this?

[sunny_talwar · Accepted solution]

How about if you do this

Date(MonthStart(Date#(Left(dim_date, 8), 'YYYYMMDD')), 'MMM/YYYY') as MonthYearCampDate,

[vipul_narang]

I used "MonthYearCampDate" from the above expression to sort the chart whereas using the "Month(Date(date#(left(dim_date, 8), 'YYYYMMDD'))) & '/' & Year(Date(date#(left(dim_date, 8), 'YYYYMMDD'))) as MonthYearCampDate," as dimension in the chart and it has solved the problem