topic Re: How to sort the numeric value Of List box. in QlikView

How to sort the numeric value Of List box.

Anonymous — Tue, 07 Feb 2017 10:32:16 GMT

Hi Community,

I have date field in below format and i have to sort this value in List box in ascending order.

e.g.

Date

2016-12,

2016-1,

2016-10,

2016-5

Expected Output

2016-1,

2016-5,

2016-10,

2016-12

Please also refer below image

Thanks in advance.

Re: How to sort the numeric value Of List box.

sunny_talwar — Tue, 07 Feb 2017 10:36:53 GMT

Is this field read as date by QlikView? May be try like this in the script

LOAD Date(Date#(Date, 'YYYY-MM'), 'YYYY-MM') as Date,

....

FROM ....;

Also, read here:

Why don’t my dates work?

Get the Dates Right

Re: How to sort the numeric value Of List box.

Anonymous — Tue, 07 Feb 2017 10:46:20 GMT

Hi Sunny,

This date field according to user want to see Year and Week number.

Already i have created date logic in edit script that is Year and Week number(2016-1).

and I also showed this value in Dashboard in List box.

but i want to proper sort order so how i sort this? that means

2016-15

2016-16

2016-17

2016-18

2016-19

like this.

Re: How to sort the numeric value Of List box.

sunny_talwar — Tue, 07 Feb 2017 10:47:35 GMT

How do you create this Year and Week field in the script?

Re: How to sort the numeric value Of List box.

tamilarasu — Tue, 07 Feb 2017 10:51:48 GMT

Hi Ishwar,

Try this as sort expression,

=Makeweekdate(Left(Field_Name,4),SubField(Field_Name,'-',2))

But best practice is to format the date field in script itself.

Re: How to sort the numeric value Of List box.

Anonymous — Tue, 07 Feb 2017 10:59:47 GMT

Hi Sunny,

I have written below code for week in edit script

Year(Date) &'-'& Week (Date) As WEEK

Date is my date field.

this WEEK field showing me below result and i want to show this WEEK in ascending sort order in List box

Pleas refer below screen shot

Re: How to sort the numeric value Of List box.

sunny_talwar — Tue, 07 Feb 2017 11:00:44 GMT

Try this:

Dual(Year(Date) &'-'& Week (Date), WeekStart(Date)) As WEEK

Re: How to sort the numeric value Of List box.

Anonymous — Tue, 07 Feb 2017 11:02:45 GMT

Hi Tamil,

I have written logic for WEEK field in edit script and i want to show that field in List box in Ascending order.

Re: How to sort the numeric value Of List box.

Anonymous — Tue, 07 Feb 2017 11:12:04 GMT

Hi Sunny,

Thanks for your help your logic is working properly. I got solution.

Please can you explain me your logic.

Re: How to sort the numeric value Of List box.

Anonymous — Tue, 07 Feb 2017 11:14:07 GMT

Hi Sunny,

Thanks for your help. your logic is working properly. i got solution.

Pleas can you explain me your logic.

Re: How to sort the numeric value Of List box.

sunny_talwar — Tue, 07 Feb 2017 12:03:55 GMT

Dual function assigns a numeric and text value to a field. For text, we are using what you wanted to see and for numeric we used WeekStart() function which basically helps you sort the text value best of the underlying numeric value.

Re: How to sort the numeric value Of List box.

Anonymous — Tue, 07 Feb 2017 12:36:06 GMT

Thanks Sunny,

Now i understood.