Sort last months (including last year months)

[#1]

Hi,

I have a line chart showing last 4 months data. (Dec'10 - Jan'11 - Feb'11 - Mar'11), but month dimension shows months sorted by its numeric value (Jan - Feb - Mar - Dec)

How could I sort these months without year information)?

[#2]

Have you considered/tried sorting by Year-Month value?

[#3]

Sorry, incomplete previous post. Sorting by Year-Month value as sort by expression in sort tab.

[#4]

hi,

one solution is to include an "ORDER BY [Month column]" in your "Load" and use the sort "Load order" for this dimension in your report.

kindly,

[#5 · Accepted solution]

I have found a possible solution:

On Sort tab, select Month dimension (called Mnt) and check Expression with the following:

if(Mnt>Month(Today()),-1*(Month(Today())-Mnt),(Month(Today())-Mnt))

[#6]

Also, for rolling N-months analytical objects, you can create an extra line in your calendar, such as:

autonumber(date(monthstart(Date), 'YYYYMM'), 99) as YMNum.

Then on set analysis side, you set the expression for your N-rolling month to, e.g.:

sum({$<YMNum={$(=max(YMNum))}, Year=, Month=, Day=>} Sales) and sum({$<YMNum={$(=max(YMNum)-1)}, Year=, Month=, Day=>} Sales),

sum({$<YMNum={$(=max(YMNum)-2)}, Year=, Month=, Day=>} Sales), etc.

Then you can set your dynamic label as you wish by copying the related set analysis statement and use in with the only() function, e.g. for the first expression above the dynamic label is: only({$<YMNum={$(=max(YMNum))}, Year=, Month=, Day=>} Month).

Hope that's what you're looking for.

[#7]

That's very useful! Thank you!