topic Re: Sort on date field in QlikView

Sort on date field

evansabres — Thu, 31 May 2018 12:43:55 GMT

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?

Re: Sort on date field

sunny_talwar — Thu, 31 May 2018 12:46:24 GMT

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?

Re: Sort on date field

evansabres — Thu, 31 May 2018 12:48:46 GMT

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

Re: Sort on date field

sunny_talwar — Thu, 31 May 2018 12:54:32 GMT

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

Re: Sort on date field

evansabres — Thu, 31 May 2018 12:56:19 GMT

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

Re: Sort on date field

evansabres — Thu, 31 May 2018 12:58:01 GMT

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

Re: Sort on date field

evansabres — Thu, 31 May 2018 14:17:11 GMT

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.