Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
bhanu99
Contributor II
Contributor II

how to sort month year in my dimension

I have a date column in the format as below:

bhanu99_0-1695842820850.png

 

I used Date(Date#("End Date",'MM/DD/YYYY'),'MMM YY')) as "End Date" to bring this into a uniform format.

Then i used  floor(date(date#("End Date",'MM/DD/YYYY'),'MMM YY')) as MonthSort in my load editor , so that i can use this monthsort i sorting the dimension.

 

It is coming as below when i  am using a table to view the values. 

bhanu99_1-1695843042508.png

How to rectify this repetitions?

Labels (1)
2 Replies
MayilVahanan

Hi

For each date, it display the Month year. so it looks correct. 

Suppose, if you want to see only one date per month, you can use MonthStart , so that, it can be achievable.

floor(MonthStart(date#("End Date",'MM/DD/YYYY')))

Date(MonthStart(Date#("End Date",'MM/DD/YYYY')),'MMM YY')

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Vegar
MVP
MVP

If you're not to picky with how MonthSort is formated then you could also use the monthname() function. This gives you what you need in an 'MMM YYYY' format. (I always use this function for this)

monthname( Date#("End Date",'MM/DD/YYYY') )