Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how to SORT >>>> =YEAR&'/'&MONTH

Hi,

I'm creating a DIMENSION with " =YEAR&'/'&MONTH " so that the year and month can be like 2010/10.

But i have a problem with sorting it.

EG:

2010/01 , 2010/10 , 2010/02 , ....

The Oct is in between Jan and Feb.

Does anyone has a solution?

Thanks.

1 Solution

Accepted Solutions
pover
Luminary Alumni
Luminary Alumni

Nick,

A function like date(makedate(YEAR,MONTH),'YYYY/MM') won't work? It should.

Or do you have access to change the script?

View solution in original post

9 Replies
pover
Luminary Alumni
Luminary Alumni

Instead of using the dimension =YEAR&'/'&MONTH use =date(DATE,'YYYY/MM') and it should be easier to sort by number.

Regards.

Not applicable
Author

Hi Karl,

Thanks for the suggestion, but i can't do that.

YEAR & MONTH that i'm using are 2 different fields, that's why i need to concate them.

pover
Luminary Alumni
Luminary Alumni

Nick,

A function like date(makedate(YEAR,MONTH),'YYYY/MM') won't work? It should.

Or do you have access to change the script?

Not applicable
Author

It works !!!!

Now, year and month are in digit. is there any way i can turn month into, jan, feb.... ?

pover
Luminary Alumni
Luminary Alumni

Just add another M

date(makedate(YEAR,MONTH),'YYYY/MMM')

Not applicable
Author

Hi,

Says now the month is store in pure text.

when i concatenate it with year and apply date() and makedate(), the field is empty.

Does anyone has any solution for this?

Thanks.

pover
Luminary Alumni
Luminary Alumni

Nick,

This depends on the text you are using to store month, but if you have a list of string values like Jan, Feb, Mar, etc. you can change a string to a date using the date#() function. For example,

date#(YEAR&'/'&MONTH,'YYYY/MMM')

Regards.

vinicius_siquei
Partner - Contributor III
Partner - Contributor III

Great answer!

Not applicable
Author

Create a New Month column in a select statement  like if Month = Jan then 1..Alias MonthID ,,,something like this and then sort by MonthID