Date in Range with parameters


I have a filter with date in range and i want to know if it is possible instead of dates to use parameters.

I want only to show the first of the month to the selected date. Exampel:

If the customer selects june 16 2019 i want to show data from 1 june to 16 of june 2019, the customer can even select a date like 5 may 2016, then i want to show the dates 1 may to 5 of may 206.

So im trying to make a sql select that takes the day the customer selects and convert it to parameters, one with the first day of that month and one to the date the customer wants to see.

Then i want my filter date in range to show the data between the parameters.

Any ideas?

Hi Steven!

You can implement this idea by using a parameter of type: date in the view file, and then build the conditional statement into a sql_always_where parameter on the explore.

Here is what my parameter looks like:

parameter: date_filter {
    type: date

and then on the explore I have:

sql_always_where: ${orders.created_date} BETWEEN date_add({% parameter orders.date_filter %},interval -DAY({% parameter orders.date_filter %})+1 DAY) 
    AND {% parameter orders.date_filter %} ;;

The solution above works for MySQL, so your specific syntax for finding the first day of the month might be different depending on your SQL dialect. The end result looks like this:

This was a cool question and use case, thanks for reaching out to ask!