Sorting not displayed for Month

[#1]

Dear QVExperts,

In a calculated dimension,

=IF(DateModeLatest='Yearly',YEARNAME(DATE(WRNTY_PUR_DT)),IF(DateModeLatest='Monthly',

//MONTHNAME(DATE(WRNTY_PUR_DT)),

Month(Date(WRNTY_PUR_DT)) & ' '& Year(Date(WRNTY_PUR_DT)),

DUAL(Pick(Match(Month(WRNTY_PUR_DT), 'Dec','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov'),

'1Q','1Q','1Q','2Q','2Q','2Q','3Q','3Q','3Q','4Q','4Q','4Q')

& If( Month(WRNTY_PUR_DT) = 12, Right(Year(WRNTY_PUR_DT),2)+1, Right(Year(WRNTY_PUR_DT),2) )

, QuarterStart( WRNTY_PUR_DT,0,12))))

When i use, MONTHNAME(DATE(WRNTY_PUR_DT)), MonthNames are not sorted. Also for January Month, only year is  displaying.

When i use, Month(Date(WRNTY_PUR_DT)) & ' '& Year(Date(WRNTY_PUR_DT)), the values are not sorted.

Please find the image below.

[#2]

When i try providing calculated dimension like this,

I am shown with all the values. But the month is not sorted.

=IF(DateModeLatest='Yearly',YEARNAME(DATE(WRNTY_PUR_DT)),

IF(DateModeLatest='Monthly',MONTH(DATE(WRNTY_PUR_DT,'YYYYDDMM')) & ' ' & YEAR(DATE(WRNTY_PUR_DT,'YYYYDDMM')),

DUAL(Pick(Match(Month(WRNTY_PUR_DT), 'Dec','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov'),

'1Q','1Q','1Q','2Q','2Q','2Q','3Q','3Q','3Q','4Q','4Q','4Q') & If( Month(WRNTY_PUR_DT) = 12, Right(Year(WRNTY_PUR_DT),2)+1, Right(Year(WRNTY_PUR_DT),2) )

, QuarterStart( WRNTY_PUR_DT,0,12))))

Year & Quarter are perfectly already. Only the issue with Month.

[swuehl]

I don't think I can follow what you are describing.

And I am not sure why you use conditional enablement of your calendar dimensions at all, and how the DateModeLates fields play a role here.

Please post a small sample QVW that demonstrates your issue.

[rubenmarin]

If only monthly is causing the issue, try applying the same logic to make it a dual value (using dual or creating a date field)

"MONTH(DATE(WRNTY_PUR_DT,'YYYYDDMM')) & ' ' & YEAR(DATE(WRNTY_PUR_DT,'YYYYDDMM'))"

Can be converted to:

Date(WRNTY_PUR_DT, 'MMM YYYY')

[#5]

Hi Swuehl,

We have a list box, DateModest, which has three values, Yearly, Quarterly & Monthly.

We have a drill down group which comprises Product Category, Client, Band, etc

Now in the chart, we have one calculated dimension, if DateModest List box values selected Yearly displays in Year. If selected Monthly displays in Month. If selected in Quarter, it displays in Quarter.

Now if selected Yearly, Quarterly, it displays correctly & also sorted correctly.

But when i use MonthName, then only for January, i could see year for ex; Jan 2013 means it display as 2013.

But when i use IF(DateModeLatest='Monthly',MONTH(DATE(WRNTY_PUR_DT,'YYYYDDMM')) & ' ' & YEAR(DATE(WRNTY_PUR_DT,'YYYYDDMM')), i have all the values displays correctly but not sorted.

So now the issue is the month not sorted.