need date range to sort as per dates not string

[Lisha_Nandwani]

I need to showcase a date range (start date - end date) (as a dimension) in the format ‘Month Day, Year’, and when applying a filter on it, the date ranges should be sort as dates not as strings.

[Quinn_Wildman]

You can do this by computing the difference as a number, then formatting it as a date. Because the difference is a number, it will sort in the order you want. I’ve not experimented with filtering - you can do that. I think computing the difference in seconds is best, because you can then divide by the total seconds in a day to get portions of a day if need be. This is a RedShift example, but other databases will be similar:

dimension: date_range {
type:number
sql:datediff(s, ${start_date}, ${end_date})/(60.0*60*24);;
value_format: "m d, yy"
}

[Lisha_Nandwani]

I found the solution for this to split the dimension:

dimension: date_range {
sql: ${start_date};;
html: {{ rendered_value | date: ‘%b %d, %Y’ }} - {{ end_date._value | date: ‘%b %d, %Y’ }};;
}

dimension: period_filter {
sql: concat(${start_date}, ’ to ', ${end_date});;
}

So here date_range will be used as the field in visualization and period_filter will be used in Dashboard Filter.
One drawback using this way is that period_filter values will be in format YYYY-MM-DD because Looker only supports YYYY-MM-DD as date format if you want the values to be treated, sorted as dates not string.