topic Re: Sorting dual numbers in QlikView

Sorting dual numbers

Fri, 12 Dec 2014 09:25:17 GMT

I have a list box containing dual numbers, they are currently not sorted from smallest to largest.

I would like it to be :

3-9

10-16

17-23

24-30

How can I do this?

Re: Sorting dual numbers

tresB — Fri, 12 Dec 2014 09:27:31 GMT

If the are really dual values, the sort should have worked numerically. Can you share a sample qvw?

Re: Sorting dual numbers

francoiscave — Fri, 12 Dec 2014 09:28:53 GMT

Hi Tumelo,

You can create the Week number in your script and then, in your Weeks filter order by expression based on your new field.

François

Re: Sorting dual numbers

Fri, 12 Dec 2014 09:31:57 GMT

This is the script I use to get the Week values

if( month(WeekStart( RE_DAY) )<> month( RE_DAY), 1 , day(WeekStart( RE_DAY) ))
&' - '&
if( month(WeekEnd( RE_DAY) )<> month( RE_DAY), day(RE_DAY) , day(WeekEnd( RE_DAY) )) AS Weeks;

Re: Sorting dual numbers

Fri, 12 Dec 2014 09:35:54 GMT

You can create a number to relate to the weeks in the script which you can then use in the listbox to sort your week buckets by

Rgds

Re: Sorting dual numbers

Fri, 12 Dec 2014 09:43:06 GMT

I tried to do that but some weeks were having the same numbers with different month days.

I wanted it to be like

3-9 Week1

10-16 Week2

17-23 Week3

24-30 Week4 for each month

How can I do this?

Re: Sorting dual numbers

Fri, 12 Dec 2014 09:51:03 GMT

Hi Tumelo, Whats in your field named: RE_DAY ? You could sort that if its the underlying day in the listbox>sort tab - otherwise could you send me an example of the above screenshot and I'll create a little lookup table for you.

Re: Sorting dual numbers

Fri, 12 Dec 2014 09:56:51 GMT

RE-DAY is a date field

Re: Sorting dual numbers

MarcoWedel — Fri, 12 Dec 2014 09:57:04 GMT

Your expression does not create dual values, to do so, you could use:

Dual(

if( month(WeekStart( RE_DAY) )<> month( RE_DAY), 1 , day(WeekStart( RE_DAY) ))
&' - '&
if( month(WeekEnd( RE_DAY) )<> month( RE_DAY), day(RE_DAY) , day(WeekEnd( RE_DAY) )),

if( month(WeekStart( RE_DAY) )<> month( RE_DAY), 1 , day(WeekStart( RE_DAY) ))

)

AS Weeks;

Using ths expression instead, you can simply sort numerically.

hope this helps

regards

Marco

Re: Sorting dual numbers

francoiscave — Fri, 12 Dec 2014 09:58:32 GMT

Tumelo,

Use the WeekName(Re_DAY) as WeekNo function in your script in the same Load statement that youor field Weeks.

Then, you could sort by WeekNo in your listbox (sort Tab).

François