Using Dropdown values as date filter

Queries and Resources
,
1

Hi all,

I am a newbie to retool and I am currently stuck with a query.

I have a dropdown menu with the following values:

image

If a user chooses "Daily" the value is ="1"

This value shall be used in my SQL query to create a flexible timerange:

select count(id),
from sales sa
WHERE sa.created_at > current_date - INTERVAL '1' DAY

And instead of using the hardcoded version of '1' I would like to substitute it with my dropdown value

However, when electing {{select1.value}} I always receive an error

select count(id),
from sales sa
WHERE sa.created_at > current_date - INTERVAL {{select1.value}} DAY (This query does not work)

Any thoughts on how to solve this problem? I appreciate all your efforts!

2

See attached. (Import as a file into your instance of retool as an app)
The dropdown contains (I believe) what you are you looking for.
I didn't build it and I don't recall who did but they get the credit.
Timeframe.json (8.7 KB)

1 Like
3

I just ran into what might have been your problem too and solved it.

See my answer on how to explicitly cast the drop down value to an interval:

DATE("publishedAt") > NOW() - {{new_videos_days.value}}::INTERVAL