topic Re: Month vs. MonthName sorting issue in QlikView

Month vs. MonthName sorting issue

Mon, 02 Jun 2014 16:46:01 GMT

Hi All,

This appears to be a bug, but I thought I would do a sanity check first. I created a small worksheet that just has data for 13 months, and a value to sum. I created two charts to show the sums. I put a sort value of =[Position Date] in the Expression for both.

If I use MonthName([Position Date]) as an expression for the dimension, it sorts correctly. I use Month([Position Date]), it puts the data for May 2014 first in the chart, followed by the other months in correct order.

I have attached the application - if anyone can tell me why this is, and how to get the sort to work correctly, I would greatly appreciate it.

Thanks

Re: Month vs. MonthName sorting issue

Colin-Albert — Mon, 02 Jun 2014 16:58:32 GMT

Have you set the sort field to sort the data as numbers.

The month() function will create a dual data value with the text of the month being displayed by default but the numeric value is also there.

Look at dual() for more information.

Re: Month vs. MonthName sorting issue

maxgro — Mon, 02 Jun 2014 17:09:13 GMT

uncheck sort by expression and use sort by numeric value

Re: Month vs. MonthName sorting issue

Mon, 02 Jun 2014 17:09:32 GMT

I have tried that in the sort tab, as well as a number of other attempts. But I still see the wrong sorting with Month(), but not with MonthName().

Re: Month vs. MonthName sorting issue

Mon, 02 Jun 2014 17:13:23 GMT

Numeric Value gives Jan - Dec, even though the years are different.

The point here is that I get different sorting in two charts that are identical except for the function in the Dimension. The sorting is correct for one, different for the other, even though the sort condition is the same on the same underlying field.

Re: Month vs. MonthName sorting issue

Mon, 02 Jun 2014 17:14:59 GMT

It is not a bug. I think it is because of the result of month function. You have 1 value for May in 2013 and 1 value for May in 2014. This appears to be the case only for May and no other months. The month function only returns the name of the month and not the year, so it is consolidating the may 2013 and may 2014 dates. and since 2013 comes before 2014 it is bringing back may first which makes perfect sense.

Re: Month vs. MonthName sorting issue

Mon, 02 Jun 2014 17:20:00 GMT

I am sorting on the Date field, [Position Date], not on Month([Position Date]).

If you open the attached document, you will see that May 2013 is filtered out and does not show in the charts. I put that in to show that it is only considering May 2014.

It appears that in the case of Month(), it is ignoring the sort condition. That is the issue.

Re: Month vs. MonthName sorting issue

Mon, 02 Jun 2014 17:29:33 GMT

month and monthname functions acts different. month returns only the name of the month where as monthname store the complete date and returns month year. thats why sorting worked in 2nd case.

Re: Month vs. MonthName sorting issue

maxgro — Mon, 02 Jun 2014 17:30:44 GMT

maybe you have to limit the date in the sort with the same condition used in expression; not sure of that, I have to check

= MonthName(

only({$<[Position Date]={"$(= '>' & AddMonths($(eLastDayOfLastMonth),-12) & '<=' & AddMonths($(eLastDayOfLastMonth),0))"}>} [Position Date])

)

Re: Month vs. MonthName sorting issue

maxgro — Mon, 02 Jun 2014 17:34:35 GMT

I suppose Colin was suggesting to use a dual in dimension as

=dual(Month([Position Date]), MonthName([Position Date]))

and this works if you sort by numeric

Re: Month vs. MonthName sorting issue

Mon, 02 Jun 2014 17:36:59 GMT

Yes u r ignoring the date but on the set statement which is on the expression level which is not going to affect the group by. Try selecting from June 2013 to May 2014 in your list box, it will do the sorting correctly . this is because u r now filtering it at the dimension level, so it now takes only from June 2013 to May 2014 when building the chart and as a result gives the correct result. If you dont select anything , even if u r ignoring the date in the set statement at the expression level, while building the chart it will be taken into account at the dimension level and henceforth it sorts from may. Another way to test what I said is to remove the date 2013 completely from the load statement and reload it and test it.

Re: Month vs. MonthName sorting issue

hic — Mon, 02 Jun 2014 17:47:45 GMT

It is indeed (sometimes) ignoring the sort condition. As it should.

[Position Date] is not a valid sort expression: The sort expression needs to be an aggregation, and since you don't have one, QlikVIew assumes Only([Position Date]) which returns NULL fairly often.

Consider the fact that one month (Month() or MonthName()) has several [Position Date]s associated - which should be used for sorting?

See more on It’s all Aggregations.

HIC

Re: Month vs. MonthName sorting issue

Mon, 02 Jun 2014 18:25:27 GMT

Thanks, all. On a deadline so I wasn't thinking clearly.