topic Re: Sorting a List Box in QlikView

Sorting a List Box

Thu, 03 Apr 2014 17:34:57 GMT

Hello

I have a list box. The dimension in that List Box is Period Name. It has the values as SEP,JAN,MAR,JUN,FEB........

How do I sort it to show JAN,FEB,MAR,APR,MAY....................DEC.

What is the best setting that I should do in my sort tab to fix this.

Thanks

Re: Sorting a List Box

swuehl — Thu, 03 Apr 2014 17:37:47 GMT

Create a dual value for example using Month(Date) in your load script, which automatically returns a dual value. Then sort numeric.

http://community.qlik.com/blogs/qlikviewdesignblog/2012/11/13/dual

Re: Sorting a List Box

PradeepReddy — Thu, 03 Apr 2014 17:39:44 GMT

Hi,

Choose auto ascending option from List box properties..

List Box Properties --> Sort --> Sort By (Choose Auto Ascending)

Thanks

Pradeep

Re: Sorting a List Box

Thu, 03 Apr 2014 17:40:55 GMT

This does not work sorry. Already tried this.

Thanks

Re: Sorting a List Box

Thu, 03 Apr 2014 17:41:40 GMT

Hello

I do not want Minth(Date). I just want the first three letters of the month.

For example JAN, FEB,....

tHANKS

Re: Sorting a List Box

its_anandrjs — Thu, 03 Apr 2014 17:42:04 GMT

For months load num value also in load like

Month -> JAN,FEB,MAR,APR,MAY....................DEC

MonthNum -> 1,2,3,4,5,6,7,8,9,10,11,12

and in list box of the sort option use MonthNum field for sort

In list box of the Month in sort -> expression

write like

=Match(Month,'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC')

Best way is add MonthNum field.

Re: Sorting a List Box

its_anandrjs — Thu, 03 Apr 2014 17:43:27 GMT

Write like the below code in Expression and uncheck other option

=Match(Month,'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC')

Re: Sorting a List Box

Thu, 03 Apr 2014 17:47:13 GMT

Hello Anand

Where should I add this line

=Match(Month,'

JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC')

In my list box?

Thanks

Re: Sorting a List Box

swuehl — Thu, 03 Apr 2014 17:52:47 GMT

Month function will return a three letter month name code, according to your settings in the load script:

From the Help:

month(date)

Month. Returns a text string representing the month when the fraction of expr is interpreted as a date, but can be formatted as a number.

Example:

month( '1971-10-30' ) returns Oct.

Basically, it will return one of the text values defined in your script, like in my setting:

SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';

Re: Sorting a List Box

its_anandrjs — Thu, 03 Apr 2014 17:53:09 GMT

In the expression option with your month field this line

=Match(Month,'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC')

See the attached snap shot.

Re: Sorting a List Box

nizamsha — Thu, 03 Apr 2014 18:25:32 GMT

usaully its predefiend like this

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

for some reason u r not getting ur month column in sorting order in listbox sort option is there u can sort ascending or descending

even u r not getting mean use dual

when we r calculating in backend i use to go for dual

in front end if i want to sort based on weight mean i will go for rank

if(Month='Jan',Dual('Jan',1),

if(Month='Feb',Dual('Feb',2),

if(Month='Mar',Dual('Mar',3),

if(Month='Apr',Dual('Apr',4),

if(Month='May',Dual('May',5),

if(Month='Jun',Dual('Jun',6),

if(Month='Jul',Dual('Jul',7),

if(Month='Aug',Dual('Aug',8),

if(Month='Sep',Dual('Sep',9),

if(Month='Oct',Dual('Oct',10),

if(Month='Nov',Dual('Nov',11),

if(Month='Dec',Dual('Dec',11))))))))))))) as Month

Re: Sorting a List Box

its_anandrjs — Thu, 03 Apr 2014 18:28:21 GMT

Hi Rizwan,

Your sorting problem identify let me know

Thanks & Regards

Re: Sorting a List Box

preminqlik — Thu, 03 Apr 2014 18:39:43 GMT

Hi there,

go to expression and write the below expression...make sure remaining check boxes are unchecked

only({1}num(Month(Date#(capitalize(left(FieldName,3)),'MMM'))))

Hope this helps

Re: Sorting a List Box

Thu, 03 Apr 2014 18:50:53 GMT

Hi,

Try dual in script say for eg

month:

load * inline [

Month

April

August

Feburary

December

January

July

June

March

May

November

September

October

];

Month_New:

LOAD DUAL(Month, New_Month) as [new_mon] inline

[Month,New_Month

April,APR

August,AUG

Feburary,FEB

December,DEC

January,JAN

July,JUL

June,JUN

March,MAR

May,MAY

November,NOV

September,SEP

October,OCT

];

Month_Num_rep:

load dual(New_Month, num) as [Month_orderd] inline

[New_Month, num

JAN,1

FEB,2

MAR,3

APR,4

May,5

JUN,6

JUL,7

AUG,8

SEP,9

OCT,10

NOV,11

DEC,12

];

Re: Sorting a List Box

Thu, 03 Apr 2014 18:53:13 GMT

Thanks

This works

On Thu, Apr 3, 2014 at 11:40 AM, Prem Kumar Thangallapally <