Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Nick,
A function like date(makedate(YEAR,MONTH),'YYYY/MM') won't work? It should.
Or do you have access to change the script?
Instead of using the dimension =YEAR&'/'&MONTH use =date(DATE,'YYYY/MM') and it should be easier to sort by number.
Regards.
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.
Nick,
A function like date(makedate(YEAR,MONTH),'YYYY/MM') won't work? It should.
Or do you have access to change the script?
It works !!!!
Now, year and month are in digit. is there any way i can turn month into, jan, feb.... ?
Just add another M
date(makedate(YEAR,MONTH),'YYYY/MMM')
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.
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.
Great answer!
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