10 Replies Latest reply: Apr 24, 2014 4:47 AM by Marco Wedel

# How to sort a custom dimension

I am "bucketing" values by CloseDate by the following formula:

=if(MonthName(CloseDate) < MonthName(vTodayDate), 'Before ' & MonthName(Today()),
if(Year(CloseDate) > Year(Today()), Year(vTodayDate)+1 & '+',

MonthName(CloseDate)))

As of today, the results show:

"Before Mar 2014"

"2015+"

Mar 2014

Apr 2014

May 2014

Jun 2014

Jul 2014

....

I want to the "2015+" value to be at the end and I can't figure out how.  I tried using the DUAL function, but couldn't make it work.  Any suggestions?

Thanks, Dan

• ###### Re: How to sort a custom dimension

Sort by expression like this

If(myDimension = '2015+', 3,

if(myDimesion ='Before Mar 2014', 1,

2

)

Sort ascending

• ###### Re: How to sort a custom dimension

Thanks, but I need the formula to be dynamic.

• ###### Re: How to sort a custom dimension

here it is

If(myDimension = Year(vTodayDate)+1 & '+', 3,

if(myDimesion ='Before ' & MonthName(Today()), 1,

2

)

as before use it in sort

• ###### Re: How to sort a custom dimension

You can try Wildmatch in Sort Sheet.

Wildmatch(

if(MonthName(CloseDate) < MonthName(vTodayDate), 'Before ' & MonthName(Today()),
if(Year(CloseDate) > Year(Today()), Year(vTodayDate)+1 & '+',

MonthName(CloseDate))) ,'Before*','*2014*','*+')

Best Regards.

Tonial.

• ###### Re: How to sort a custom dimension

Thanks Fernando.  This code sorts the data, but not in the desired way.  "Before Mar 2014" stays on top, but the sorting of the Months/Years get out of order, and the "2015+" shows up close to bottom of the list, but not the very bottom, where I want it.

• ###### Re: How to sort a custom dimension

Maybe check Sort by Expression and Number Value.

• ###### Re: How to sort a custom dimension

Hi Dan,

try this sorting expression

```If(SortValue like '*+*',
3,
If(SortValue like '*Before*', 1, 2)
)
```

and sort by "numeric value" also

The result looks like this:

with these values given:

```tabSortValues:
SortValue
'Before Mar 2014'
'2015+'
];

AutoGenerate 5;
```

hope this helps

regards

Marco

• ###### Re: How to sort a custom dimension

any luck so far?