Add dynamic filters to charts

App Building
1
  • Goal: Add numerous filters to charts based on use cases defined below

Context

We are a business that books doctors appointments for patients and allows patients to reschedule appointments at their will. Our database gets populated with all bookings. Whenever a patient reschedules a booking, the old booking is still an entry in the database, but it has a rescheduled_booking_uuid field now containing an id that references a normal booking id. In other words, all columns that have a null value is reschedule_booking_uuid are the final booking. Once we remove any bookings that have reschedule_booking_uuids, we get the total count of bookings on our site.

In addition, our booking status can be "no show," "canceled," "confirmed," and "completed" where confirmed means we have confirmed it with the doctor's office, and completed where the patient has gone to the doctors and completed the appointment.

We do cosmetic bookings and medical bookings. When patients book, we classify their main reason for the visit, e.g. annual physical, dermatology, etc., all of which I want to group into "Medical" as well as Botox visits / other "Cosmetic" appointments.

I'm trying to create a dashboard for our company to show:
total bookings made per week, per month, etc.
break down by booking type "completed," "canceled," "no show" etc. - this is the status column in our table
break down by visit types i.e. "Cosmetic" vs. "Medical"

Goal 1: add a broad range of filters to charts. how do I do this? I can add it to queries but don't know how to bring in filters to my charts after querying only once

I've been able to create some of these filters in tables but essentially none of them in a chart. I query the data and filled my table with myquery.data, in addition to setting the chart to be myquery.data. I added a filter to the table which successfully filters the table, but when I set the chart data to be table1.data, it is the complete dataset, not the filtered dataset.

I have made a second query where I do the filtering in the query and then set my chart to be query2.data, but this seems like an unelegant option. You'd run two queries, not 1.

Goal 2: Add columns / breakdown chart by week of year

Furthermore, I've made use of the created_at column, which is a date-time column based on when the customer creates the appointment (not the date of the appointment, but when they booked it on our site). We are a start up and want to know how much traffic we are getting on a weekly basis. In Microsoft Excel, I can make use of the WeekNum function, but I'm not sure if/how to bring these values into Retool. For example, I want to group all of the appointments by the week that they were booked, so we can see over the course of the year how many bookings are being made on our site by week / by month. This is something that I can do easily in Microsoft excel but am really struggling with on Retool.

Goal 3: sort data into Medical / Cosmetic columns

Last, I want to group our bookings into Medical / Cosmetic. Once again, I know how to make a column in Excel based on whether another column, namely visit_reason, contains the words "botox" or "cosmetic," then it is a Cosmetic Visit, otherwise it is a medical visit. I think I should do a custom column and make it {{currentSourceRow.visit_reason.includes("Botox")}} but also not sure how to check for multiple visit reasons here. And this statement will return true/false rather than "Medical" or "Cosmetic." Maybe I need to make another column then checking whether this column returns true or false.

Any help would be greatly appreciated. I don't have JSON code to share but any that you can help me put together would be greatly, greatly appreciated. Thank you!

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