How to sort a custom dimension

[#1]

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

[arsal_90]

Share your qvw

[alexandros17]

Sort by expression like this

If(myDimension = '2015+', 3,

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

2

)

Sort ascending

[fernando_tonial]

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.

[#5]

Thanks, but I need the formula to be dynamic.

[#6]

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.

[alexandros17]

here it is

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

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

2

)

as before use it in sort

[fernando_tonial]

Maybe check Sort by Expression and Number Value.

[MarcoWedel]

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:
LOAD * Inline [
SortValue
'Before Mar 2014'
'2015+'
];

LOAD
  MonthName(AddMonths(Date#('01.03.2014','DD.MM.YYYY'),RecNo()-1)) as SortValue
AutoGenerate 5;

hope this helps

regards

Marco

[MarcoWedel]

any luck so far?