Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Share your qvw
Sort by expression like this
If(myDimension = '2015+', 3,
if(myDimesion ='Before Mar 2014', 1,
2
)
Sort ascending
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.
Thanks, but I need the formula to be dynamic.
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.
here it is
If(myDimension = Year(vTodayDate)+1 & '+', 3,
if(myDimesion ='Before ' & MonthName(Today()), 1,
2
)
as before use it in sort
Maybe check Sort by Expression and Number Value.
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
any luck so far?