Sorting not displaying

[#1]

Dear QV Experts,

In my calculated dimension, i use

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

IF(DateModeLatest='Monthly',MONTHNAME(DATE(WRNTY_PUR_DT)),

PICK(MATCH(PICK(MATCH(Left(MONTHNAME(DATE(WRNTY_PUR_DT)) , 3), 'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),1,2,3,4,5,6,7,8,9,10,11,12),12,2,1,3,4,5,6,7,8,9,10,11),

'1Q','1Q','1Q','2Q','2Q','2Q','3Q','3Q','3Q','4Q','4Q','4Q') & IF(Left(MONTHNAME(DATE(WRNTY_PUR_DT)),3) ='Dec' , RIGHT(MONTHNAME(DATE(WRNTY_PUR_DT)),2)+1 , RIGHT(MONTHNAME(DATE(WRNTY_PUR_DT)),2))

))

Now when i display for multiquarters, it is not sorting properly,

Please suggest me.

Thanks,
Sasi

[migueldelval]

Hi Sasi,

What are you doing? And Why.

Regards

Miguel del Valle

[swuehl · Accepted solution]

I think you want to create something like a finanancial quarter & year. This is something you should do in the script, not in a calculated dimension.

You can use the Dual() function to create a dual value with a numeric representation, then sort your dimension numeric:

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 )

)

[Anonymous]

Hi Sasi,

Order by Expression:

=Max({1}WRNTY_PUR_DT)

Regards!

[Digvijay_Singh]

Not sure but you may think of simplifying it by using drill down group dimension after creating appropriate fields of Year, Quarter, Month in the script.

[#6]

Thanks Swuehl. It worked for Quarter Now. But Month having still issues. I will create a new thread for month now.

[Anonymous]

Hi Sasi,

With the expression you will be able to sort your chart with only one expression.

Regards!