topic Re: Sorting a list box with months in QlikView

Sorting a list box with months

Fri, 13 Sep 2013 14:53:30 GMT

Hi,

I'm gonig slightly mad with this problem. I have created the field below:

Month(Date(Date(AddMonths(Date#(MonthYear,'MMM YYYY'),[Mth Report Period])),'MMM YYYY')) as ReportMonth1,

MonthYear e.g. 'Apr 2012'

[Mth Report Period] is a number e.g. 1

This would give the result Mar

I then use the following field to get a number:

lookup('ID','Month',Text(Month(Date(Date(AddMonths(Date#(MonthYear,'MMM YYYY'),[Mth Report Period])),'MMM YYYY'))),'MonthLookup') as MonthSort,

the lookup table basically starts with Apr as 1 and ends with Mar as 12, i then sort the data in the script by MonthSort.

I am then puting the field ReportMonth1 in a list box, but I cant get Apr first and Mar last.

If I tick the box to say load order, or do an expression =Only(Data.MonthSort).

Can someone please help as this is driving me crazy!!

Thanks in Advance,

Simon.

Re: Sorting a list box with months

Fri, 13 Sep 2013 15:25:16 GMT

I use the expression within the sort tab. I see you used a field called ID to create the presentation order.

Alternatively, you could order your load and use this to drive the sort.

I hope this helps and I haven't missed the point. Let me know how you get on.

Regards

Steve

Re: Sorting a list box with months

Fri, 13 Sep 2013 15:30:51 GMT

Hi Steve,

Thanks for the quick reply.

In my scrpit i do an order by on the field MonthSort, which when you look at the data table puts all the apr first, may second, jun third etc...

But then when I tick the box to sort by original load it does not come back in the order expected. I then try to use the expression to sort by MonthSort, but that does not work either. I would be greatful, if you could look at my current document and try and get it to sort.

Once again, thank you for the quick reply.

Cheers

Simon

Re: Re: Sorting a list box with months

Fri, 13 Sep 2013 15:51:27 GMT

You just caught me before I went home. I'll just put a little bit of code into the end of your report, this just reloads the Data Table and puts it in order of ID, and then renames the table. Looking at it, it's similar to something you have in the "Main 10" tab but this is using Data.MonthSort which is a field that doesn't look to contain any data.

I haven't been able to do a quick check due to lack of time and I can't access the source data. Can you reload and let me know how you get on please? Fingers crossed this time.

Steve

Re: Sorting a list box with months

Mon, 16 Sep 2013 14:47:25 GMT

Hi Simon

I just wondered if the above was the correct answer. Did it prove useful at least?

Regards

Steve