Dynamic date range?

Is there an option that allows dashboard users to select dynamic range to view data on their dashboard? I checked the parameters option and filter option, but it is not exactly that. We need to allow our nontechnical dashboard users to set up date ranges to view their data.

Example: https://co.vid19.sg/dashboard

Check out date-range parameters in our documentation.

There’s an example of date-range parameters in use at 10:10 in the following YouTube video:

Thank you so much! I tried to do it but am getting an error.

Since I load my data source from the google sheet, I am creating a new, 2nd query and using parameters in this query (while using 1st query as a data source. This 1s query is simple, it loads the table from google sheets in its entirety, no parameters).

So, I selected “My query results” as a data source and trying to create a new query for dynamic data range:

    SELECT Submission ID, User ID, Submission Date and Time, Tides, Animal
    FROM query_2
    WHERE
      Submission Date and Time >= ' {{ Date.start }}'
      AND query_2.Submission Date and Time <= ' {{ Date1.end }}'

I selected Date and Date 1 as parameters, Date and Time with seconds.

But I am getting the following error:

Error running query: missing values for Date, Date1 parameters.

My data format in that Submission Date and Time column is like this: 2/14/2019 18:15:00

A few more questions,:

1. Is there an option to select all columns from the query?

2. Also when the initial table in google sheet updates, how will update work in this situation? The initial query will update and then this query will update too?

3. For my user dashboard, I will have to base my dashboard and all visualizations on this second query if I want users to be able to select dynamic data range?

4. Finally, if I want to create several dashboards for several users, but do not want them to make a selection of user or see other users dashboard, using user_id parameter will not work since it is reflected on a dashboard and lets users to select a user and to see dashboards of other users? I have user_id column in my table in Google Sheets and it is numerical, like 3 or 45 etc.

Sorry for asking all these stupid questions, I am newbie and just trying to learn more about redash. Thank you in advance.

You have a typo in your query. One of your parameters is called Date and the other is Date1. For a date range parameter you will have a pair called Date.start and Date.end. Also, delete the extra space in front of your parameters. It will cause issues.

Yes. QRDS uses SQLite.

SELECT * FROM query_2

The query to Google Sheets refreshes every time you execute your QRDS query. That is unless you replace query_2 with cached_query_2 in your query text.

Correct.

You will need to create separate queries with a hard coded filter in each. Redash is built for internal teams. So if a user can run a parameterized query, they can do so with any allowable parameter value.

Thank you so much. I changed it, but still get the error:

Error running query: missing value for Date parameter.

Code:
SELECT * FROM query_2
WHERE
Submission Date and Time >= ’ {{ Date.start }}’
AND query_2.Submission Date and Time <= ’ {{ Date.end }}’

And here is a part of query_2, to show that there is column called “submission Date and Time”. Maybe the problem is in date format in query_2? But it looks absolutely normal there.

From your screenshot it seems like the problem is this: you haven’t entered a parameter value in the widget! The error message makes sense

Ok, I apologize for being completely retarded, I am a newbie and it has been a bit hard to navigate. So I selected Date and Type “Date and Time Range” in settings and selected the range in widget. Now I get Date syntax error.

My date/time is in the following format in the csv table: MM/DD/YY HR: MIN

Is this incorrect format?

Also, I am a bit confused about that widget. It allows me to select the range, let’s say the past 12 months. But will the user be able to select the range on their dashboard, let’s say October 1 October 30 , 2019? Because I want to let the user to select date range on their dashboard as well.

There’s an extra space in front of your parameter marker. That’s what causes the syntax error.

To set an arbitrary date just click the dates themselves instead of the lightning bolt glyph.

I do not understand, I copied it from this:

SELECT a, b c
FROM table1
WHERE
  relevant_date >= '{{ myDate.start }}'
  AND table1.relevant_date <= '{{ myDate.end }}'

and here it is and I still get syntax error:

SELECT *
FROM query_2
WHERE
  Submission Date and Time  >= '{{ myDate.start }}'
  AND query_2.Submission Date and Time  <= '{{ myDate.end }}'

Again, the error from your database is still pretty clear:

FROM Submission Date and Time

That’s not a valid column identifier (you can’t use spaces in column names unless you wrap them in quotes).

Perhaps it should be "Submission Date and Time"?

Oh, it worked! Thank you so much!

hi @cetalingua @jesse i have query db clickhouse : SELECT *
FROM tableWHERE datepaid >= {{ lol.start }} and datepaid <= {{ lol.end }}
GROUP BY product_name

but error

why?

You need to wrap your parameters in quotation marks. Otherwise clickhouse evaluates 2020-12-20 as integer subtraction (==1988).

Rewrite like this and it should work:

SELECT *
FROM tableWHERE datepaid >= '{{ lol.start }}' and datepaid <= '{{ lol.end }}'
GROUP BY product_name