topic Re: Re: how to sort the order of values in a list box manually in QlikView

how to sort the order of values in a list box manually

arixooo123 — Sun, 13 Jul 2014 06:19:07 GMT

hello dears,

I have a list box including the Shamsi Months ( Months in Persian),

I tested all of the sort options (state,expressions,frequency...) but it does not get sorted as i wanted it to be.

I want to order them as it is in calender, apparently i have to sort them manually. but how?

thank you

Re: how to sort the order of values in a list box manually

aveeeeeee7en — Sun, 13 Jul 2014 07:01:46 GMT

Hi arman

In the Sort Options you'll see Expression, Check that Option and paste this Expression Accordingly:

Dual("Your Month Field",match("Your Month Field" ,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'))

Since, I don't know Shamsi Months, so set accordingly and sort in Ascending or Descending Order.

Suppose, if you want Jan after Mar than do change in Expression Order.

Here, Month is your Shamsi Months Field Name.

Replace Month with your Application Month Field.

Hope that helps.

Regards

Aviral Nag

Re: how to sort the order of values in a list box manually

arixooo123 — Sun, 13 Jul 2014 07:12:06 GMT

Thank You for your answer, what if i use month field as a dimension in a chart? suppose i want to see the sales trends of year 2013, how can i reorder the month fields there?

Re: how to sort the order of values in a list box manually

aveeeeeee7en — Sun, 13 Jul 2014 07:20:20 GMT

For this case, use the Same Expression.

Dual("Your Month Field",match("Your Month Field" ,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'))

Here, your Your Month Field is the Dimension Month which you have used in your chart.

Regards

Aviral Nag

Re: how to sort the order of values in a list box manually

arixooo123 — Sun, 13 Jul 2014 07:45:26 GMT

i did what you said for sorting the month list box, but it did not change at all, what do you think might be the reason?

what can i do?

i checked the code a couple of times. i even copied your scripts and just changed the month names

Re: how to sort the order of values in a list box manually

aveeeeeee7en — Sun, 13 Jul 2014 07:47:13 GMT

Can you attach your application here. I'll from my end.

Re: how to sort the order of values in a list box manually

ashfaq_haseeb — Sun, 13 Jul 2014 08:35:38 GMT

Hi,

use the below expression for sorting

=match("Your Month Field" ,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

If possible try to share sample data.

Regards

ASHFAQ

Re: Re: how to sort the order of values in a list box manually

arixooo123 — Sun, 13 Jul 2014 08:56:36 GMT

Unfortunately, I don't know how to attach a file, but my original table is like this:

Year	Season	Month	SubID	City
2014	spring	jan (actually in Persian calendar 'فروردین')	113333	city 1
2013	winter	feb('اردیبهشت')	112222	city 2
2012	summer	...	114444	city 3

though the problem applies to other fields(Year,Season) but since the number of them are limited, it is not important that they are not sorted, but month list box has 12 values and it is much more convenient to have them sorted.

when i use the last scripts, it does not sort the months and also by selecting months in list box, they move with no order. it is weird.

Re: how to sort the order of values in a list box manually

ashfaq_haseeb — Sun, 13 Jul 2014 09:35:42 GMT

Hi,

Still I would suggest you to upload sample document with some data.

SeePreparing examples for Upload - Reduction and Data Scrambling for how to do that.

Regards

ASHFAQ

Re: Re: how to sort the order of values in a list box manually

arixooo123 — Sun, 13 Jul 2014 09:49:25 GMT

dear ashfagh, thanks for the tip

here is the sample data. the field of month is : [Shamsi Month],

Re: how to sort the order of values in a list box manually

ashfaq_haseeb — Sun, 13 Jul 2014 09:54:12 GMT

Hi,

Go to chart properties, --> Sort --> use expression for sorting and use the below expression.

=Match([Shamsi Month,'فروردین','اردیبهشت','Mar in arabic','','')

make sure to use all Arabic month names.

by the way there was no data attached.

Regards

ASHFAQ

Re: how to sort the order of values in a list box manually

arixooo123 — Sun, 13 Jul 2014 09:58:54 GMT

they are not Arabic Months, by the way, i tried that as the first effort. doesn't work

Re: how to sort the order of values in a list box manually

ashfaq_haseeb — Sun, 13 Jul 2014 10:00:38 GMT

Hi,

sharing sample qvw file with data and expected output would be worth.

Regards

ASHFAQ

Re: how to sort the order of values in a list box manually

arixooo123 — Sun, 13 Jul 2014 10:02:55 GMT

check again, the attached file is there!

thanks

Re: how to sort the order of values in a list box manually

ashfaq_haseeb — Sun, 13 Jul 2014 10:06:44 GMT

Hi,

I have downloaded it. But its corrupted, I cannot open it.

Can you attached another version that i can open.

Regards

ASHFAQ

Re: how to sort the order of values in a list box manually

arixooo123 — Sun, 13 Jul 2014 10:08:16 GMT

My expected output is instead of the picture shown in the first question, months should have this order: from right to left

فروردین--اردیبهشت--خرداد--تیر--مرداد--شهریور--مهر--آبان--آذر--دی--بهمن--اسفند

Re: Re: how to sort the order of values in a list box manually

ashfaq_haseeb — Sun, 13 Jul 2014 10:19:09 GMT

Hi,

Look at the attached image and QlikView file.

Hope it helped.

Regards

ASHFAQ

Re: Re: how to sort the order of values in a list box manually

arixooo123 — Sun, 13 Jul 2014 10:21:54 GMT

try this one :

Re: Re: how to sort the order of values in a list box manually

arixooo123 — Sun, 13 Jul 2014 10:26:57 GMT

that's all correct.. but i cannot open your qvd file, since i'm using the personal edition. if you tell me what you wrote in the expression part, i would be grateful

Re: Re: Re: how to sort the order of values in a list box manually

ashfaq_haseeb — Sun, 13 Jul 2014 10:37:59 GMT

Hi,

Create a new qvw file and use the below script

load * Inline

[

Month,Year,Amount

فروردین,2013,500

اردیبهشت,2013,500

خرداد,2013,500

تیر,2013,500

مرداد,2013,500

شهریور,2013,500

مهر,2013,500

آبان,2013,500

آذر,2013,500

دی,2013,500

بهمن,2013,500

اسفند,2013,500

فروردین,2014,500

اردیبهشت,2014,600

خرداد,2014,600

تیر,2014,600

مرداد,2014,600

شهریور,2014,600

مهر,2014,600

آبان,2014,600

آذر,2014,600

دی,2014,600

بهمن,2014,600

اسفند,2014,600

]

Save and reload

Now drop your Month Field list box to your front end application.

Right click on month properties sort tab --> Select load order reversed.

Look at capture.jpg image attached.

use the below expression to sort

=Match(Month,'اسفند','بهمن','دی','آذر','آبان','مهر','شهریور','مرداد','تیر','خرداد','اردیبهشت','فروردین')

Create bar chart with month as dimension and sum(Amount) as expression

Again go to chart properties --> sort tab of chat--> Select load order reversed.

use the below expression to sort

=Match(Month,'اسفند','بهمن','دی','آذر','آبان','مهر','شهریور','مرداد','تیر','خرداد','اردیبهشت','فروردین')

Look at final output capture1.jpg

Regards

ASHFAQ