How to get sort for MonthYear Axis using Expression/Text/Numeric check option to work

[vvira1316]

Hi,

I'm not sure why sort is not working as intended in following scenario

Dimension Expression

=Month(Date([Date of Review],'MM/DD/YYYY')) & ' ' & Year(Date([Date of Review],'MM/DD/YYYY'))

Sort Tab

The expression on Sort Tab is

=Num#(Year(Date([Date of Review],'MM/DD/YYYY')) & If(Len(Match(Month(Date([Date of Review],'MM/DD/YYYY')), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))))=1,
'0'&Match(Month(Date([Date of Review],'MM/DD/YYYY')), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))),Match(Month(Date([Date of Review],'MM/DD/YYYY')), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))))
,'######')

Now if I change Dimension expression to

=Num#(Year(Date([Date of Review],'MM/DD/YYYY')) & If(Len(Match(Month(Date([Date of Review],'MM/DD/YYYY')), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))))=1,
'0'&Match(Month(Date([Date of Review],'MM/DD/YYYY')), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))),Match(Month(Date([Date of Review],'MM/DD/YYYY')), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))))
,'######')

and Sort Tab as follows

then I get correct sort order on chart axis as can be seen in following screen shot.

I will appreciate any help / guidance

[#2]

try:

=aggr(

Num#(Year(Date([Date of Review],'MM/DD/YYYY')) & If(Len(Match(Month(Date([Date of Review],'MM/DD/YYYY')), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))))=1,
'0'&Match(Month(Date([Date of Review],'MM/DD/YYYY')), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))),Match(Month(Date([Date of Review],'MM/DD/YYYY')), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))))
,'######')

,[Date of Review])

[shraddha_g · Accepted solution]

Just Try with Monthstart field if you have.

else Monthstart(Date([Date of Review],'MM/DD/YYYY'))

[vvira1316]

Hi Shraddha,

It resolved once I used =MonthName(Date([Date of Review],'MM/DD/YYYY')) in dimension and Numeric Value in sort.

Thanks for your input.

Regards,

Vijay