topic Re: List box sorting in QlikView

List box sorting

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: List box sorting

Anonymous — Fri, 10 Feb 2017 17:38:41 GMT

is it a dual() value?

Re: List box sorting

Anonymous — Fri, 10 Feb 2017 17:40:50 GMT

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

Re: List box sorting

Anonymous — Fri, 10 Feb 2017 17:45:44 GMT

LOAD

...

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

...

--> then sort your listbox by number

Re: List box sorting

Fri, 10 Feb 2017 17:48:00 GMT

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.

Re: List box sorting

Anonymous — Fri, 10 Feb 2017 17:48:47 GMT

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

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

Re: List box sorting

Anonymous — Fri, 10 Feb 2017 17:51:26 GMT

Thanks, updated code below:

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

Re: List box sorting

Anonymous — Fri, 10 Feb 2017 17:54:02 GMT

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

Re: List box sorting

Anonymous — Fri, 10 Feb 2017 17:54:07 GMT

does it work?

Re: List box sorting

Anonymous — Fri, 10 Feb 2017 17:59:28 GMT

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.

Re: List box sorting

Anonymous — Fri, 10 Feb 2017 18:04:26 GMT

I see. So I do have calendar date in my table. I didn't realize I could sort in my expression based on other columns.

This works:

=Calendar_Date

Re: List box sorting

Anonymous — Fri, 10 Feb 2017 18:06:05 GMT

that's why I asked for dual()...

Re: List box sorting

Anonymous — Fri, 10 Feb 2017 18:06:40 GMT

Yup! That's the easier way. Glad we were able to get you there.

Re: List box sorting

MarcoWedel — Fri, 10 Feb 2017 23:19:05 GMT

Hi,

jsut load as dual and forget about sorting expressions, instead sort numerically:

an example using the already suggested Dual() function with a small correction of the month part to deliver correct dates:

LOAD Dual(Quarter,MakeDate(Mid(Quarter,4),Mid(Quarter,2,1)*3-2)) as Quarter
INLINE [
    Quarter
    Q1 2013
    Q2 2013
    Q3 2013
    Q4 2013
    Q1 2014
    Q2 2014
    Q3 2014
    Q4 2014
    Q1 2015
    Q2 2015
    Q3 2015
    Q4 2015
    Q1 2016
    Q2 2016
    Q3 2016
    Q4 2016
    Q1 2017
    Q2 2017
    Q3 2017
    Q4 2017
];

hope this helps

regards

Marco