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?
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!
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.
Any progress on this feature? I am trying to use the order_by_field parameter in desc order in my dimension
please prioritize this feature!
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
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