How to sort months across a year change?

[#1]

I am creating a table that has sum(Sales) for the last 12 months by using this formula: 

Sum(if(CloseDate <= Floor(vLoadDate) AND CloseDate > (Floor(AddMonths(vLoadDate,-12)) - Floor(Day(vLoadDate))+1), Sales))

I want to use just the first letter of each month.  My question comes with sorting.  As of today, a normal sort would show Nov & Dec (Sales Month) of LAST year after the current month of October...when I want to show Nov & Dec of last year BEFORE January of this year.  What is the best way to sort this dimension (Sales Month)?  Being that this is a rolling-12 month chart, I would want all of last year’s month sorted before this year’s month.

Any thoughts?  Thanks!

[d_pranskus · Accepted solution]

Hi

Create the following dimension in you script and use it in your chart:

DUAL(LEFT(TEXT(MONTH([Sales Date])), 1), YEAR([Sales Date]) * 100 + MONTH([Sales Date])) AS [Sales Month Cont]

Cheers

[#3]

Thanks a buch!

[#4]

Now that I have the dates, I’m trying to create a stacked bar chart with the dimension I am stacking called  Segment.  However, I don’t have a variable name to put in the denominator…

Sum(Sales) / Sum (Total <???> Sales)

I tried my Sales_Month and Sales_Date fields, but neither worked.  The chart renders, but the bars aren’t staking to 100%.  Any suggestions?