Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a cyclic group dimension for Year, Quarter & Month Year in a bar chart.
I need to show the dimensions in the calendar order such as
2014,2015,2016 for year
Q1, Q2, Q3, Q4 for Quarter
Jan-2015, Feb-2015....Dec-2015.
I need to sort all these dimensions into calendar order. I have tried to sort based on the expression of Min(Date), but it doesn't work for quarters.
Could anyone please advise me on this?
Thanks & Regards,
Karthikeyan.
Create the quarters and month-year values as duals, for example, in your calendar:
...
Dual('Q' & Ceil(Month(CalDate) / 4), Ceil(Month(CalDate) / 4)) As Quarter,
Date(MonthStart(CalDate), 'MMM-yyyy') As MonthYear,
...
Now you can sort these fields numerically to get the correct order.
Create the quarters and month-year values as duals, for example, in your calendar:
...
Dual('Q' & Ceil(Month(CalDate) / 4), Ceil(Month(CalDate) / 4)) As Quarter,
Date(MonthStart(CalDate), 'MMM-yyyy') As MonthYear,
...
Now you can sort these fields numerically to get the correct order.
If you need your month-year to span multiple years, you could add a month-sequence field, or define the month-year o behave like a sequence number:
...
Dual(Date(CalDate, 'MMM-yyyy'), Year(CalDate)*12 + Month(CalDate)) As MonthYear,
...
This will ensure that Jan-2016 follows after Dec-2015.
Hi,
Can you attach the script that you are using to generate this fields?
For Year and Month select Number in Sort tab & for Quarter select Text in Sort tab.
Regards,
jagan.
Perfect!!! It worked...
Thanks much.
Awesome.. Thanks a ton