topic Sorting Values In a List Box in QlikView

Sorting Values In a List Box

Wed, 03 Mar 2010 18:02:32 GMT

Hi all,

I have another thing I am stuck with!!!

I am creating a qlikview report for records returned within a certain period. I am using a standard SQL smalldatetime field named 'DateReturned' and would like to create a new list box which shows this data grouped into the year and month, for example, the values 03/03/2010 and 02/03/2010 I would like to appear as 'March 2010' (or something similar).

I would like to also be able to sort this list box in descending order so that it keeps the sorting integrity of the dates, for example the first six values in this list box would be:

March 2010

February 2010

January 2010

December 2009

November 2009

October 2009

Can anyone help?

Thanks in advance,

Chris

Sorting Values In a List Box

pbaenen — Wed, 03 Mar 2010 18:07:25 GMT

Are you using a Calendar in your application? Because if you are, you can add a field that just holds the first day of the month for each date, and you can use that in your application. In my calendar file, I put:

Date(MakeDate(Year(Date), Month(Date)), 'MMM-YYYY') as MonthYearFormatted

Sorting Values In a List Box

Wed, 03 Mar 2010 18:13:34 GMT

Hi Paul,

I don't have a calendar in my application as I have forgot how to create one! I went on a course 3 years ago and have only just been given the opportunity to use the application!!

Any ideas on how to create the calendar off my field named DateReturned?

Thanks,

Chris

Sorting Values In a List Box

Miguel_Angel_Baeyens — Wed, 03 Mar 2010 18:23:39 GMT

Hello Chris,

Check among many others this post of today. There are a lot of good examples on how to make a calendar and some tips to customize it.

Sorting Values In a List Box

pbaenen — Wed, 03 Mar 2010 18:27:23 GMT

Yes, you can either add this to your script:

Date(MakeDate(Year(DateReturned), Month(DateReturned)), 'MMM-YYYY') as MonthYearFormatted

Or you can use that as a Calculated Dimension in a chart if you want to see what it looks like without reloading.