Month vs. MonthName sorting issue

[#1]

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

[Colin-Albert]

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.

[maxgro]

uncheck sort by expression and use sort by numeric value

[#4]

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().

[#5]

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.

[#6]

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.

[#7]

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.

[#8]

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.

[maxgro]

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])

)

[maxgro · Accepted solution]

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