Add dynamic filters to charts

App Building
2

Hi @tommyg - welcome to the forums! Whenever anyone asks about charts, I always point them to an excellent primer here. Overall, with some JavaScript you can do whatever you have been doing in Excel. There will certainly be a learning curve if you are not already familiar, but I'd argue it would be worth your time.

As to your specific goals:

Goal 1 - assuming you have gone through the primer and created a JS query that returns data and layout for the chart(s), you can connect your filters to the section of the query creating the data output. Assuming your base data is myquery.data and you set a filter in a component called selectFilter, you can add something along the lines of formatDataAsArray(myquery.data).filter(obj => obj.criteria == selectFilter.value) in that section of the code. You would also need to add an event to trigger the chart query on "blur" of the filter selection.

Goal 2 - for any date, there are many ways to represent it using JavaScript in Retool. One straightforward way is to use Moment. For example, if your date is data.created_at you can use moment(data.created_at).format("YYYY_ww") which will return 2024_[Zero padded week number] and you can use that to group your data. Many options here, so you will need to consult the docs to get to the format you want (format() tokens info here).

Goal 3 - You can take a couple of approaches. A simple extension of what you have to get a true/false but based on more criteria would be :

// You can hard-code and add to the array as needed or reference data in an array from somewhere
["Botox","Cosmetic"].some(item => currentSourceRow.visit_reason.includes(item))

Or, if you want to extend a bit further to map to a type instead of a true/false, the following might help get you started:

// You can hard-code and add to the array as needed or reference data in an array from somewhere
const cosmeticCriteria = ["Botox", "Cosmetic"];   
const medicalCriteria = ["Migraine", "Medical"]; 

let procedureType = "Unknown";

if (cosmeticCriteria.some(item => currentSourceRow.visit_reason.includes(item))) {
  procedureType = "Cosmetic";
} else if (medicalCriteria.some(item => currentSourceRow.visit_reason.includes(item))) {
  procedureType = "Medical";
}

return procedureType;

In addition to (or instead of) adding the filters and classifications to the data portion of the query for your plots, you may want to think about adding transformers in your queries to format/manipulate the data prior to sending it to the charts. That way, you can also reference whatever formats/manipulations you have done in your tables and other displays as well.

I hope this helps give you some ideas as your move forward. Implementing anything I've suggested will take some doing, but it's hopefully not as daunting as I might have made it seem.

1 Like