I have a pie chart that is build from the results of a SQL query as below:
I would now like to add the ability to filter the SQL query results by a date range.
I have added a Date Range component to the app but do not know how to proceed?
I have tried the following.
I've learnt previously that in order to pass parameters to my SQL queries I need to call them using a JS script to pass the parameters in. I have created user_stats_js_refresh_stats with additionalScope like so:
user_stats_sql_onboarding_status.trigger({
additionalScope : {
from: user_stats_date_range.value.start,
to: user_stats_date_range.value.end
}
});
I have set up the Date Range component to call this script on Change
I've updated the underlying query to use the from and to dates. However changing the date range has no effect on the pie chart though.
try using an query json with sql resource type doing something like the following in the
SELECT * FROM {{formatDataAsArray(yourTable.value)}}
WHERE ((your_date_column_name >= {{ yourDateRangeField.value.start}} and value_date <= {{ yourDateRangeField.value.end}}) OR ({{yourDateRangeField.value.start == ''}} AND {{yourDateRangeField.value.end == ''}}))
And set it to run when inputs change and then use it as the source for the pie chart... this way you won't have to use additional scope
You should be able to do this because you could do the following:
Your original query I assume is one that hits the Postgres db/MySQL db directly.... take the result of this and store it in a temp state then query yourTempState.value. I do this all of the time...
Also, try not to double post.... let's see if we can get this issue solved for you first before you post again
As you can see I do not disable converting queries...
So, again
hit your db DIRECTLY with query that uses Postgres resource.