Sort by Month

[#1]

In my load script, I create a YearMonth field as follows: date(feeddate,'YYYYMM') as YearMonth.  I have another field, AsOfYearMonth, that is equal to YearMonth.  In my report, I have this calculated dimension - =date(AsOfYearMonth,'MMM').  The report output so far looks like this:

=date(AsOfYearMonth,'MMM') //=dual(text(date(AsOfYearMonth,'MMM')), //num(month(AsOfYearMonth))) //=Month(AsOfYearMonth)	2011 Total Voluntary	dual(text(date(AsOfYearMonth,'MMM')), num(month(AsOfYearMonth)))
Feb	7.4%	2
Mar	15.4%	3
May	11.6%	5
Sep	-	9
Apr	-	4
Oct	-	10
May	-	5
Mar	-	3
Jan	14.3%	1
Dec	-	12
Feb	-	2
Apr	14.1%	4
Jun	-	6
Jul	-	7
Jun	11.1%	6
Nov	-	11
Aug	-	8
Jan	-	1

My problem is that some months (Jan, Feb, Mar, Apr, May, Jun) are repeating, instead of consolidating onto one line. I suspect that this is due to the actual value behind each month.  How can I force these repeating months onto the same line?

[erichshiino]

Hi,

Try to create a field in the script with the syntax:

month(feeddate) as Month,

or date(month(feeddate),'MMM') as Month

In your case, I'd say your are right, you have a value with month and year behind the dimension, even if you format it in a different way.

Hope this helps,

Erich

[#3]

Create a Field Month(Date) as MonthName in your Date Dimension and use that.

[#4]

Thank you.  In both cases, this should be done in the load script, not in the report itself, correct?

[erichshiino]

Yes, that you go in the script.

You can also include the month as an integer if you still have problems to sort it

num(month(date)) as nMonth

Hope it helps,

Erich

[#6]

Thank you both.  That worked.