order_by_field descending?

[jess_kyle-16327]

Is there a way to use the order_by_field parameter in my dimension, but specify that it should sort Descending rather than Ascending?

I have a dashboard filter called Cohort Label. This is a text value, but sorted by a date field (${user_created_at_date}). I don’t want to use a date-type filter, because I like the list of text value that the user can simply select.

However, I want these to be sorted descending, and there appears to be no way of making this happen. I don’t understand why; this seems pretty simple. Can anyone suggest a workaround?

[christina_blake]

Great timing - we’re also trying to figure this one out to sort dates as strings with most recent at the top! I don’t have an answer but following this for sure!

[IanT]

Looked around at docs and to me doesn’t seem to be a way to do it so you will have to create a new hidden dim which represents the date as a number and use that as the order_by_field. If you want it to order the other way around then just do 9999999-number.

[JVFrancis]

Any progress on this feature? I am trying to use the order_by_field parameter in desc order in my dimension

[iulianvs]

please prioritize this feature!

[blue1]

If you want to sort a date in descending order, all you have to do is make more recent dates a smaller number than older dates. To achieve this, you can simply convert the date into a negative number:

dimension: inverted_date {
hidden: yes
type: number
sql: DATEDIFF(day, '1900-01-01', ${user_created_at_date}) * -1 ;;
}

We convert the date into a number by counting the days between it and some arbitrary old date like '1900-01-01'. This will return an integer that we can multiply by -1.

In this way, larger dates will have a larger negative number and sort lower than small dates. We have essentially flipped the sort order upside down.

Now all you have to do is use the inverted_date as the new order_by_field and your values will be sorted in DESC order.

Hope this helps,

blue

 

[JVFrancis]

Thanks a lot @blue1  That works! I had to change the LookML a bit as per my need/BigQuery dialect and it works as expected!
DATE_DIFF( ${user_created_at_date}, '1900-01-01', DAY) * -1