Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
vipul_narang
Contributor II
Contributor II

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

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!

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

How about if you do this

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

View solution in original post

7 Replies
astoulov
Contributor
Contributor

Hi, 

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

astoulov
Contributor
Contributor

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

MonthSort.jpg

vipul_narang
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

Thanks Sunny.

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

sunny_talwar

How about if you do this

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

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