topic Re: Sorting date in listbox in QlikView

Sorting date in listbox

curiousfellow — Tue, 15 Dec 2015 08:20:08 GMT

I have a field containing a textstring respresenting a period, like this : Van 01/02/2014 t/m 31/01/2015

Now I want a listbox containing the month and year sorted descending with this format 'YYYY-MM'

I tried several options some in the load script and some as an expression in the sort tab

The listbox shows the right values (2015-09,2015-08,2015-07 etc) but not in the right order

Per year at first the months with 31 days were shown and then the months that have 30 days, and februari at last

As said , I used various options like the num function, date functions, format functions.

Any suggestions ?

Re: Sorting date in listbox

prieper — Tue, 15 Dec 2015 08:30:57 GMT

Did you check the Properties -> Sort and then "Numeric Value" Descending?

Peter

Re: Sorting date in listbox

Tue, 15 Dec 2015 08:32:01 GMT

In the use this option:

in expression sort use this function :

=num(Date('2015-08'&'-01'))

you have to replace '2015-08' with your field

i hope it works.

Re: Sorting date in listbox

tamilarasu — Tue, 15 Dec 2015 08:33:30 GMT

Hi,

Try sort expression as

Year(Date#(Datefield,'YYYY-MM'))+ Month(Date#(Datefield,'YYYY-MM'))

Change Datefield to your data field name.

Re: Sorting date in listbox

HirisH_V7 — Tue, 15 Dec 2015 08:52:41 GMT

Hi,

In Back end at edit script you can ,

Example script :

Data:

LOAD *,

Num(Month(Date)) as Month,

Year(Date) as Year,

Num(Month(Date))&'-'&Year(Date) as MonthYear

INLINE [

Date

01/01/2015

02/01/2015

03/01/2015

04/01/2015

05/01/2015

06/01/2015

07/01/2015

08/01/2015

09/01/2015

10/01/2015

11/01/2015

12/01/2015

13/01/2015

14/01/2015

15/01/2015

16/01/2015

17/01/2015

18/01/2015

19/01/2015

20/01/2015

21/01/2015

22/01/2015

23/01/2015

24/01/2015

25/01/2015

26/01/2015

27/01/2015

28/01/2015

29/01/2015

30/01/2015

31/01/2015

01/02/2015

02/02/2015

03/02/2015

04/02/2015

05/02/2015

06/02/2015

07/02/2015

08/02/2015

09/02/2015

10/02/2015

11/02/2015

12/02/2015

13/02/2015

14/02/2015

15/02/2015

16/02/2015

17/02/2015

18/02/2015

19/02/2015

20/02/2015

21/02/2015

22/02/2015

23/02/2015

24/02/2015

25/02/2015

26/02/2015

27/02/2015

28/02/2015

29/02/2015

30/02/2015

31/02/2015

01/03/2015

02/03/2015

03/03/2015

04/03/2015

05/03/2015

06/03/2015

07/03/2015

08/03/2015

09/03/2015

10/03/2015

11/03/2015

12/03/2015

13/03/2015

14/03/2015

15/03/2015

16/03/2015

17/03/2015

18/03/2015

19/03/2015

20/03/2015

21/03/2015

22/03/2015

23/03/2015

24/03/2015

25/03/2015

26/03/2015

27/03/2015

28/03/2015

29/03/2015

30/03/2015

31/03/2015

];

Calendar Script:

 Num(date(Date,'DD/MM/YYYY')) as Date_Ord,

Order By Date_Ord asc

Output:

PFA

Hope this Helps,

Regards,

Hirish

Re: Sorting date in listbox

MK_QSL — Tue, 15 Dec 2015 09:13:46 GMT

Create Period Field in Script as below

Date(MonthStart(YourDateField),'YYYY-MM') as Period

Now Create a List Box for Period Field.

Go to Sort Tab

Sort By Numeric Value (Ascending or Descending Order)

Re: Sorting date in listbox

Tue, 15 Dec 2015 09:13:46 GMT

On the Sort Tab,

sort by numeric value -> descending

load order -> original

text -> A->Z

Re: Sorting date in listbox

curiousfellow — Tue, 15 Dec 2015 12:15:43 GMT

This one worked for me. Thank you very much