Sorting issue

[karthikeyan1504]

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.

[jonathandienst · Accepted solution]

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.

[jonathandienst]

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.

[jagan]

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.

[karthikeyan1504]

Perfect!!! It worked...

Thanks much.

[mahendrankr]

Awesome.. Thanks a ton