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.

[rubenmarin]

Hi Sasi, those values are strings so the numeric sort won't work, to keep the numeric internal value you can use a date with a format, ie:

"MONTHNAME(DATE(WRNTY_PUR_DT))" can be translated as "DATE(WRNTY_PUR_DT, 'MMM YYYY')

[swuehl]

Why don't you use MonthName() ?

MonthName(WRNTY_PUR_DT)

or if you like another format:

Date(Monthstart(WRNTY_PUR_DT),' YYYY-MMM' )

Both create dual values and should be easy to sort in your chart.

[#4]

Hi Swuehl, Ruben,

When i use MonthName & Date(MonthStart(WRNTY_PUR_DT), i am getting below.

Dec2012 shown as 1Q13

Jan2013 shown as 2013

Feb2013 is correct

Mar2013 shown as 2Q13

Apr2013 is correct.

Please suggest me.

[swuehl]

Seems somehow that your if() statement executes different branches per date.

How is DateModeLatest related to e.g.Product Category or other relevant fields?

Try with a single function in your calculated dimension:

MonthName(WRNTY_PUR_DT)

Do you still see different formats and sorting not correctly?

[#6]

Hi Swuehl, Ruben,

When i try going for individual dimensions for Yearly, Quarterly & Monthly if 'Enable Conditional' All Yearwise, Quarterwise, Monthwise values are sorted properly as expected.

But my DrillDown Group is not showing the icon to further drill down.

Please suggest me.

[swuehl]

You don't have a drill down group created for your calendar fields, do you?

[#8]

No Swuehl. It is only for Product Category, Client, Band ,etc [For Dimension columns]

[rubenmarin]

As for my understanding your issue is that adding the 3 conditional dimensions (I suppose only one is enabled at a time) then your drill-down stops working.

I tried with a very simple sample and I didn't found that issue. Maybe there is some change that affected values at some level of the group? If you remove the conditional dimensions it works again?

[#10]

Yeah Ruben,  If more than one dimensions though it is conditional, then drill-down stops working.