topic Re: How to sort a custom dimension in QlikView

How to sort a custom dimension

Mon, 24 Mar 2014 16:22:49 GMT

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

arsal_90 — Mon, 24 Mar 2014 16:26:19 GMT

Share your qvw

Re: How to sort a custom dimension

alexandros17 — Mon, 24 Mar 2014 16:27:13 GMT

Sort by expression like this

If(myDimension = '2015+', 3,

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

)

Sort ascending

Re: How to sort a custom dimension

fernando_tonial — Mon, 24 Mar 2014 16:31:33 GMT

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

Mon, 24 Mar 2014 16:39:13 GMT

Thanks, but I need the formula to be dynamic.

Re: How to sort a custom dimension

Mon, 24 Mar 2014 16:40:28 GMT

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

alexandros17 — Mon, 24 Mar 2014 16:41:45 GMT

here it is

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

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

)

as before use it in sort

Re: How to sort a custom dimension

fernando_tonial — Mon, 24 Mar 2014 17:07:19 GMT

Maybe check Sort by Expression and Number Value.

Re: How to sort a custom dimension

MarcoWedel — Mon, 24 Mar 2014 20:15:28 GMT

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

Re: How to sort a custom dimension

MarcoWedel — Mon, 31 Mar 2014 11:51:52 GMT

any luck so far?

Re: How to sort a custom dimension

MarcoWedel — Thu, 24 Apr 2014 08:47:36 GMT

Please close this thread if your question is answered.

thanks

regards

Marco