How to Create Dashboard Applications in Retool

Create a SQL query named 'getKPIs'. This query returns a single row with aggregate metrics for the selected date range. Reference the date range picker using {{ dateRangePicker1.value }} — Retool date pickers return an array [startDate, endDate]. Enable 'Run when inputs change' so the query re-runs automatically when the date range changes. Do not run on page load from this query — let the date picker's default value trigger it.

1-- getKPIs query
2SELECT
3  COUNT(*) AS total_orders,
4  SUM(amount) AS total_revenue,
5  AVG(amount) AS avg_order_value,
6  COUNT(DISTINCT customer_id) AS unique_customers,
7  SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END)::float / COUNT(*) * 100 AS return_rate
8FROM orders
9WHERE
10  created_at >= {{ dateRangePicker1.value[0] }}
11  AND created_at <= {{ dateRangePicker1.value[1] }};

Drag a Date Range Picker component to the top of the canvas. Set its Default start date to the first day of the current month: {{ moment().startOf('month').toISOString() }}. Set its Default end date to today: {{ moment().toISOString() }}. All queries that filter by date should reference {{ dateRangePicker1.value[0] }} and {{ dateRangePicker1.value[1] }}. With 'Run when inputs change' enabled on each query, changing the date range instantly refreshes all charts and KPIs.

Drag 4 Statistic components into a horizontal row at the top of the canvas. For each one, set the Statistic value to a field from getKPIs: {{ getKPIs.data[0].total_revenue }}, {{ getKPIs.data[0].total_orders }}, etc. Set the Label text to a human-readable name ('Total Revenue', 'Total Orders'). Use the Prefix field for currency symbols ('$') and the Suffix field for units ('%' for return rate). Set the Format to 'Number' and configure decimal places. Statistics components also support a trend delta — bind it to compare current vs prior period.

1// Statistic component values:
2// Total Revenue: {{ getKPIs.data[0]?.total_revenue?.toLocaleString('en-US', { maximumFractionDigits: 0 }) }}
3// Total Orders: {{ getKPIs.data[0]?.total_orders }}
4// Avg Order Value: {{ getKPIs.data[0]?.avg_order_value?.toFixed(2) }}
5// Return Rate: {{ getKPIs.data[0]?.return_rate?.toFixed(1) }}

Create a second query 'getRevenueTrend' that groups revenue by day or week within the selected date range. Drag a Chart component below the KPI row. Set its Data source to {{ getRevenueTrend.data }}. Configure X axis to the date column and Y axis to the revenue column. Set the chart Type to 'Line' and enable 'Show area'. Add a title in the Inspector. The chart will automatically refresh with the date range because getRevenueTrend references the same dateRangePicker1 inputs.

1-- getRevenueTrend query
2SELECT
3  DATE_TRUNC('day', created_at) AS period,
4  SUM(amount) AS revenue,
5  COUNT(*) AS order_count
6FROM orders
7WHERE
8  created_at >= {{ dateRangePicker1.value[0] }}
9  AND created_at <= {{ dateRangePicker1.value[1] }}
10GROUP BY 1
11ORDER BY 1;

Create a third query 'getTopCategories' that returns revenue by category. Add a second Chart component set to 'Bar' chart type showing category breakdowns. Use the Group By feature in the Chart Inspector to aggregate data if the query returns raw rows. For a horizontal bar chart, swap X and Y axes and set the Orientation to 'Horizontal' in the chart Inspector. This gives users a quick visual breakdown without reading a table.

At the bottom of the dashboard, add a Table component bound to a 'getOrders' query that shows individual orders with pagination. Add a search Text Input above the table and reference it in the query: WHERE ... AND (customer_name ILIKE {{ '%' + search.value + '%' }} OR order_id::text ILIKE {{ '%' + search.value + '%' }}). Enable server-side pagination in the query using LIMIT and OFFSET tied to {{ table1.pageSize }} and {{ table1.pageIndex * table1.pageSize }}.

In each query's settings, scroll to the Advanced section and find 'Polling interval' or 'Auto-run'. Set all dashboard queries (getKPIs, getRevenueTrend, getTopCategories) to auto-run every 60 seconds. This keeps the dashboard fresh without requiring the user to manually refresh. For true real-time data, see the WebSocket tutorial — polling every 60 seconds covers most internal dashboard use cases at minimal query cost.