Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
ShellyG
Creator
Creator

Sorting Date Descending with empty values on the bottom

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

Labels (5)
1 Solution

Accepted Solutions
Or
Master
Master

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.

View solution in original post

4 Replies
Or
Master
Master

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
Creator
Creator
Author

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
Master
Master

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.

View solution in original post

ShellyG
Creator
Creator
Author

Hi!

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