Make a calendar with month year (Nov 2010, Dec 2010, Jan 2011 etc).

[#1]

Hi,

I want to create a calendar that uses the format MMM YY, so each calendar post contains both month and year.

For the moment I can create a month calendar or a year calendar, but once i add these two together the posts get all unsorted.

What i want to reach is a calender like this:

Nov 10, Dec 10, Jan 11, Feb 11 etc.

I thought i might use Year(OrderDate) & Month(OrderDate) AS OrderMonthYear, but the result is unsorted and I have not found a way to sort my calendar. It either sorts it by letters (putting April first) or by month disregarding year (putting Dec 10 and Dec 11 next to each other).

[#2]

Try MonthName(YourDateField) as MonthYear

Thanks.

[googel84 · Accepted solution]

If I understood correctly, I already faced this issue and solved using an expression to sort the value. I used something like

date#('01' & num(month(OrderDate),'00') & year(OrderDate),'DDMMYYYY')

This way each month-year couple is treated for sorting purpose as the first day of the relevant month.

[#4]

Hello,

this was't too bad: Year(OrderDate) & Month(OrderDate) AS OrderMonthYear. But I would use the dual() function in order to create a MonthYear. Look at this little exam (not syntax checked):

load . . . dual( Month(OrderDate) & Year(OrderDate), num(Year(OrderDate)) & num(Month(OrderDate)) AS MyMonthYear;

dual() has two parameters (string, num) for both representations and of course num sorting.

HtH, Roland

[#5]

Thank you, I understand how dual works, where there's one parameter for the sorting (num) and one for the presentation (string).

There's still a problem tho, using num(month) will give 1,2....11,12, add that value to the year will give the result 20095 (may), 200912 (dec), but oct, nov, dec will end up in the end clumped up together since the number "200911" is bigger than "20105" (twohundred tousand ninehundred eleven versus twenty tousand onehundred five).

I need to somehow get the zero before the single digit months, like 05, 06, 07... 11, 12 to get all the numbers to be twohundred tousand instead of just twenty tousand. (201005, 201006... 201011, 201012.

[#6]

Problem solved. I used both of your suggestions combined to a perfect answer

dual( Month(OrderDate) & ' ' & Year(OrderDate), num(Year(OrderDate)) & num(Month(OrderDate), '00' )) AS OrderMonthYear,