topic Re: How to sort date in MMM/YYYY format in Proper Order in App Development

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

vipul_narang — Sat, 16 Nov 2024 06:51:18 GMT

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!

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

astoulov — Tue, 15 Jan 2019 10:44:22 GMT

Hi,

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

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

astoulov — Tue, 15 Jan 2019 11:05:27 GMT

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

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

vipul_narang — Tue, 15 Jan 2019 11:15:38 GMT

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,

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

sunny_talwar — Tue, 15 Jan 2019 12:08:27 GMT

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.

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

vipul_narang — Tue, 15 Jan 2019 13:21:35 GMT

Thanks Sunny.

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

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

sunny_talwar — Tue, 15 Jan 2019 13:27:44 GMT

How about if you do this

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

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

vipul_narang — Tue, 15 Jan 2019 14:04:39 GMT

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