Question

Date in Range with parameters

  • 16 July 2019
  • 4 replies
  • 631 views

Hello!


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?


4 replies

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!

@jon.allen - Thanks for sharing the above. I was trying to have a parameter that will sit on multiple views, but wasn’t able to do so, do you have a use case where a parameter was implemented across multiple views.

Additionally, I want to use a date interval (like between 1 to 90 days) when joining two views

Userlevel 6
Badge +1

I do have a parameter across different view. It was for currency conversion. The idea was brought by Looker Support and I am extremely happy with the result!

First, I created a “bare view” with only the parameter:

view: parameter_conversion_currency {

parameter: fx_currency {
label: "Conversion Currency"
type: unquoted
default_value: "1"
allowed_value: {
label: "Euro"
value: "fx_rate_eur"
}
allowed_value: {
label: "US Dollars"
value: "fx_rate_usd"
}
}
}

What it does for me is that the value is the column that will be selected for the conversion.

Next, you create an empty join

join: parameter_conversion_currency {}

Then, I can use this parameter in the view:

dimension:fx_rate {
type: number
hidden: yes
sql: {% if parameter_conversion_currency.fx_currency._parameter_value == "1" %}
1
{% else %}
${TABLE}.{% parameter parameter_conversion_currency.fx_currency %}
{% endif %} ;;
}

Unfortunately I couldn’t reduce the code even more to contain the IF statement inside the parameter view because it needs the current view context when parameter is selected in the explore.

But at least I didn’t have to define the same parameter in every single view. Now if I add a new currency column, I add it in one place and it’s available everywhere.

You’d have to see if this approach can be adapted to your specific needs but it’s worth a try!

 

Userlevel 1

I am trying to do the same with snowflake for 3 months of data but i dont think the above works. I tried changing the dialect but couldnt find the right one.

Reply