List box sorting

[Anonymous]

How can I sort the below by the latest quearter? For this instance the latest quarter is Q1 2017. I tried all the sorting options and none give me the results I want.

[Anonymous]

is it a dual() value?

[Anonymous]

Had to look up dual value. No this is not a dual value.

[Anonymous]

LOAD

...

dual(yourquarterfield,MakeDate(right(yourquarterfield,4),4*mid(yourquarterfield,2,1)) as yourquarterfield,

...

--> then sort your listbox by number

[#5]

Under sort options use expression. Convert your QN yyyy format into yyyyqn and it should work. Other crude option is to use if condition in the sort expression. If ( variable= q1, 1, if q2 , 2 .... It's multiple nested if option in case you are unable to convert the data in sortable way.

[Anonymous · Accepted solution]

On the sort tab.  In the Sort by Expression, you could do this.

=right(yourfield,4) & ' '& mid(yourfield,2,1)

[Anonymous]

Thanks, updated code below:

dual(CALENDAR_QUARTER,MakeDate(right(CALENDAR_QUARTER,4),4*mid(CALENDAR_QUARTER,2,1)))

[Anonymous]

This works perfectly! Could you explain what it's doing?

[Anonymous]

does it work?

[Anonymous]

It's taking the year number, that's this part:  right(F1,4)  and the quarter number, that's this part mid(yourfield,2,1).

So the entire formula would take this: "Q4 2014" and give you this "2014 4".

And it would do it for all your values.

Sorting that gives you the order you're looking for.

Really, you should probably just have another column in your data model that is easily sortable for this.  Like a month or day column in your calendar.  And put that in the sort expression.

But what I gave you works in a pinch.