Sort chart columns by day of week

App Building
1

Hello Retool community,

My company is a startup that helps customers book doctor's appointments. We want to know how much traffic we are getting on a weekly and monthly basis. I know I need to use 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). 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/month 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.

I think I need to use a custom column, but a. not sure if it's even possible and b. how to write the code and bring it into my chart. Thank you in advance.

2

Hi @tommyg,

Where is your data? In a database, if so, which one? If your data is in a Retool DB (or Postgres), you could run a query like this:

SELECT 
    created_at,
    EXTRACT(WEEK FROM created_at) AS week_of_year,
    EXTRACT(MONTH FROM created_at) AS month_of_year,
    (EXTRACT(DAY FROM created_at) - 1) / 7 + 1 AS week_of_month
FROM 
    your_table;

Which will create those columns for you directly in the query.