Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
karthikeyan1504
Creator III
Creator III

Sorting issue

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.

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

5 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jagan
Luminary Alumni
Luminary Alumni

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
Creator III
Creator III
Author

Perfect!!! It worked...

Thanks much.

mahendrankr
Contributor III
Contributor III

Awesome.. Thanks a ton