topic Re: Sorting Date Descending with empty values on the bottom in App Development

Sorting Date Descending with empty values on the bottom

ShellyG — Sat, 16 Nov 2024 17:27:08 GMT

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

Re: Sorting Date Descending with empty values on the bottom

Or — Wed, 24 Feb 2021 10:17:49 GMT

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.

Re: Sorting Date Descending with empty values on the bottom

ShellyG — Wed, 24 Feb 2021 10:47:59 GMT

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!

Re: Sorting Date Descending with empty values on the bottom

Or — Wed, 24 Feb 2021 12:40:33 GMT

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.

Re: Sorting Date Descending with empty values on the bottom

ShellyG — Wed, 24 Feb 2021 14:29:50 GMT

Hi!

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