Quick question, I have a bar chart where the x-axis is a date and the y-axis is amount (in USD). Currently, the chart shows all my data since a year ago, how can I create a filter component to allow my end user to set a date range and for the chart to then only show data within this date range. As of now, I am able to create a date filter but I have no idea how to connect the resulting dateRange.value.startDate to my chart. Thoughts? I tried using a trigger but I am unsure if the trigger should update my query or my chart. In my query I have a variable for start_date but I am unsure how to trigger changes to this value.
Sounds like you're on the right lines with this thought process and what you're trying to do is definitely possible.
Consider that your query gets all the data and is then available within your app - you can display that data in a chart, as you are doing, you can also filter that data without needing to rerun the query.
Without being able to see your data structure this might not be quite correct for you but the broad way to do this would be:
Set the data source of you chart to be the data property of your query, formatted as an array
Add a filter to the data source to match the selected date range picker.
eg data source for the chart: {{ myQuery.data }}
then becomes something like
// filter the data set where created date is before the chosen date picker value
{{ myQuery.data.filter(x => x.created_on_date < dateRangePicker.value) }}
If you want to handle the case where there isn't a date picked (ie all data) then you could start to add in some additional logic here too
// if a date has been chosen use it, otherwise use the full data set
{{ (dateRangePicker.value) ? myQuery.data.filter(x => x.created_on_date < dateRangePicker.value) : myQuery.data }}
Hope this points you in a suitable direction - I would add that comparing dates and different date formats is often more challenging than it first appears so don't panic if you don't get this working perfectly the first time.
Thanks for sharing a very good example. I have tried adding your script into my chart but unfortunately I am getting a type error that filter is not a function. Here is how I added it to my chart's data source.
Can I assume there is an issue with my dataset? It seems to be returing an array of data (attached below) so I am not too sure what is going on here. Sharing a screenshort of the output when running dailyBookings
Looks like that dataset isn't an array, you can use formatDataAsArray to convert it though either here or in your query transformer - the chart component accepts arrays or objects of arrays (which is what you currently have and most queries produce)
Hi Dave!
Piggy-backing on this, as I'm a frittata when it comes to this stuff:
We want to look at the number of quotes to filter each week for the last 6 weeks, as well as monthly for the last 6 months.
Is there an option to say all quotes in the last 42 days and bucket them by week?
Or what is the best option for that?
Thank you for all your input on the original question. Super helpful!
Not an expert here, but what I did was to run a query that will return results with columns
Year - Month
Year - Week
Date
Number of quotes
In this case, when you create you chart, you can let the x-axis be either 1, 2 or 3. Whenever you filter the chart for a specific date range, it will automatically have the right number of buckets. Hope this was clear enough, else let me know where you are stuck.
Should be doable if you query that information, as @Shiva mentions! The query will depend on your resource
Here's a Retool Database query example where I'm filtering the query for all results in the past year & then I'm able to dynamically change my chart to show day, week, or month data:
