Sort on date field

[evansabres]

I have a drill-down group called 'Calendar'

within that group there are two dimensions, 'transMONTH' and 'transDATE'

Within the group settings, I have created a sort order for 'transMONTH' by using an expression =Month(Date#(transMNTH2017,'MMM')) so that the months appear in ascending order.

My transDATE field is in the format, 'M/D/YYYY' (9/10/2017') and when I try to utilize the same expression =Day(Date#(transDATE2017', 'DDD')) the dates do not sort as expected.

Am I going about this incorrectly?

[sunny_talwar]

What is 2017 here?

=Day(Date#(transDATE2017', 'DDD'))

Is that a field or are you trying to give 2017 as a static value to your date? But doesn't date already have year value?

[evansabres]

The name of the fields are 'transDATE2017' and 'transMONTH2017'

[sunny_talwar · Accepted solution]

Oh okay, why don't you just let is sort numerically for transDate2017? Since this is already a date field... it should work

[evansabres]

Yes, I have tried this, hence the question here.

[evansabres]

Oh, i see you are saying numerically, without the expression. I will try that although that is what I believe I started with before attempting an expression

[evansabres]

I think this may work, but I am having major issues with this date field, due to the data source. Will need to go back to the source to see if I can have any control on how we receive that field. Appreciate the help.