Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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')
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.
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.
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?
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.
You don't have a drill down group created for your calendar fields, do you?
No Swuehl. It is only for Product Category, Client, Band ,etc [For Dimension columns]
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?
Yeah Ruben, If more than one dimensions though it is conditional, then drill-down stops working.