Sorting Date Descending with empty values on the bottom

[ShellyG]

Dear all, 

I am trying to sort the rows in a simple table object by the first column, which is a simple date field in the following format - 'DD-MMM-YYYY'. 

I want them to be sorted descending - the oldest dates to be on top, going down to the newest dates and on the bottom we should see the ones that do not have a date. 

When I try to sort descending, it puts the ones without a date on top and then it starts from oldest to newest. 

Can someone help me with an expression which is going to sort from oldest to newest and the ones without a date to be on the bottom of the table? 

Best Regards,

Shelly

[Or]

Sorting by a date field, ascending, should show oldest first, newest last, and null values at the bottom. I've just confirmed this on some test data and it works fine. Make sure you're sorting by numeric value rather than text and that your field is actually recognized as a date in your Qlik app. 

[ShellyG]

Thanks for your reply! 

It is recognized as a date for sure and the steps are exactly the steps that I follow.  I believe the reason might be the fact that the ones without a date are not null, they are just empty rows. 

Any idea for this specific case? 

Thanks! 

[Or · Accepted solution]

If they're empty as in, a blank text (same as  using ''), it'd probably be a good idea to replace this with null in your script - if(len(YourDate)=0,null(),YourDate) for example.

If they're outright missing, for example because they're from a different logical table that doesn't have any rows for a specific key, I think it should still get sorted correctly because that should be treated as a regular null value. However, if it is not, you could try manually setting the sort order, e.g. with a formula such as Alt(YourDate,99999) which translates to "If there's no value in YourDate, fill in a value corresponding to the year 2173". Of course if you are using dates way off in the future you may need to use a bigger number than 99999 to be sure it's last.

[ShellyG]

Hi!

This method worked perfectly - Alt(YourDate,99999). Thank you a lot!