Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
How about if you do this
Date(MonthStart(Date#(Left(dim_date, 8), 'YYYYMMDD')), 'MMM/YYYY') as MonthYearCampDate,
Hi,
What data type is your month dimension in the data model? Date or string?
In case you have time dimension in Date format you can create master item MonthYear:
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,
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.
Thanks Sunny.
I tried this but it changed the line chart. any suggestions how to fix this?
How about if you do this
Date(MonthStart(Date#(Left(dim_date, 8), 'YYYYMMDD')), 'MMM/YYYY') as MonthYearCampDate,