order_by_field descending?

[anonymous]

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!

[blue]

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

[akshatrami]

I have date and time both attached in my column how this is going to work for me ? I have tried using it but not working for me can you help ? @blue1 @JVFrancis

[blue]

Have you tried casting/converting the datetime to date?

DATEDIFF(day, '1900-01-01', CAST(${user_created_at_datetime} AS DATE)) * -1 ;;

[akshatrami]

No I haven’t as I need both for my report. For instance I have time brackets for work in my company and the data is in that format like (00:00:00 year-month-day) so not sure how this is going to work as I already tried the one you have mentioned.

[blue]

This works:

dimension: datetimes {
  type: string
  sql: ${TABLE}.datetimes ;;
  order_by_field: datetimes_sort_order
}

dimension: datetimes_sort_order {
  type: string
  sql: DATEDIFF(minute, '1970-01-01', CAST(${datetimes} AS DATETIME))  * -1 ;;
}

[akshatrami]

Its still not working for me

[tawfikypc2017]

Hi there ,
I have this porposing

In the main data file, add a new column(named data_sort) and number it according to the month in this variable. For example,
Jun-20
Give it the number 1, then
Jul-20
Give it the number 2, until you reach the end of the list of months.

In the “Metric” , add this variable “data_sort” and select the “Max” option in the properties as shown in the Pic

[blue]

You made a mistake. CAST() has no argument START_TIME, it needs to be DATETIME. Also don’t forget to add the ‘order_by_field’ subparamter to the dimension ‘START_TIME’.

dimension: START_TIME {
  type: string
  sql: ${TABLE}.START_TIME ;;
  order_by_field: START_TIME_ORDER
}

dimension: START_TIME_ORDER {
  type: string
  sql: DATEDIFF(minute, '1970-01-01', CAST(${START_TIME} AS DATETIME))  * -1 ;;
}

[akshatrami]

I have tried this but it is showing me empty values when i drop down both the parameters

[blue]

Can you send me a screenshot like the one I made? Show me both the filter and the two dimensions in a tile:

[akshatrami]

Here you go

[blue]

Ok, can you also show me the two dimensions as columns in a tile (like in my screenshot)? If the input to START_TIME is null then these fields would also show null.

[akshatrami]

My query is throwing error and not running says -
invalid identifier ‘MINUTE’

[blue]

Try using DATEDIFF() not DATEDIF(). Those are two difference functions.
I have also updated the type of the order dimension to number for correct sorting.

dimension: START_TIME {
  type: string
  sql: ${TABLE}.START_TIME ;;
  order_by_field: START_TIME_ORDER
}

dimension: START_TIME_ORDER {
  type: number
  sql: DATEDIFF(minute, '1970-01-01', CAST(${START_TIME} AS DATETIME))  * -1 ;;
}