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

[#1]

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.

[pover]

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

Regards.

[#3]

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 · Accepted solution]

Nick,

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

Or do you have access to change the script?

[#5]

It works !!!!

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

[pover]

Just add another M

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

[#7]

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]

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]

Great answer!

[#10]

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