topic Re: How to get sort for MonthYear Axis using Expression/Text/Numeric check option to work in QlikView

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

vvira1316 — Tue, 29 Nov 2016 02:50:02 GMT

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

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

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

Tue, 29 Nov 2016 03:24:47 GMT

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

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

shraddha_g — Tue, 29 Nov 2016 04:23:20 GMT

Just Try with Monthstart field if you have.

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

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

vvira1316 — Tue, 29 Nov 2016 14:56:19 GMT

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