Making transform dynamic and user-selectable

[treesloth]

Hello.  I have a straightforward bar graph visual.  I generally want to display one year's data, displayed monthly.  However, when certain filters are applied, the data becomes limited to 1 month, which just produced one very wide bar.  Not very useful.  So, I'd like the option for a user to switch that to a daily view instead.  It's simple enough to present a filter to the user containing "Monthly" and "Daily", but is there a way to make the visual use that as its transform?  Thank you.

[balajij8]

@treesloth You can handle it by pushing the dynamic grouping down to the dataset level using a query parameter in the dashboard. You can use a parameterized SQL query that uses a CASE expression evaluating a granularity parameter switching between the function to aggregate by month and casting the timestamp directly to a DATE for daily granularity. It outputs a single pre-aggregated period column that you can feed into the visual.

SELECT
  'Monthly' AS granularity
UNION ALL
SELECT
  'Daily'

You can define a string parameter on the main dataset (:granularity, default to Monthly with allowed values Monthly and Daily). In the query, apply a conditional CASE statement on the timestamp column to yield the period column, then aggregate the metrics and group by that period. Alongside it, create a secondary lookup dataset with the two rows (Monthly and Daily) to populate the filter UI.

SELECT
  CASE WHEN :granularity = 'Monthly' THEN DATE_TRUNC('month', files_datetime) ELSE CAST(files_datetime AS DATE) END AS files_period, 
  SUM(fares) AS files_fare, 
  COUNT(*) AS files_count 
FROM 
  workspace.files.files_fares 
WHERE 
  files_datetime >= '2016-01-01' 
  AND files_datetime < '2016-02-01'

Configure a single-select filter using the seeded parameter control pattern on the dashboard. Wire the filter's field encoding to the lookup dataset to display the clean labels and bind its parameter encoding to the :granularity parameter on the primary dataset.

Now set up the bar chart with the period column on the x-axis ensuring the scale type is set to temporal so dates sort and scale chronologically and the aggregated measure on the y-axis. When you switch the filter between Monthly and Daily, the dataset re-executes with the selected parameter value, automatically switching the chart's granularity without distorting the bar widths

[hayoni]

Hi @treesloth 

You can easily do this by using a dashboard parameter to handle the date grouping directly in your SQL query, while keeping the chart's Transform setting as None. Here is the step-by-step setup:

Step 1: Create a dataset for the dropdown options

Create a SQL dataset to build the dropdown list:

• Dataset name: options_view_mode

SELECT 'Monthly' AS view_mode
UNION ALL
SELECT 'Daily' AS view_mode

Step 2: Add the parameter and logic to your main dataset

Add a parameter named p_view_mode (Default: "Monthly"), then use a CASE statement to switch the date:

• Dataset name: dataset_sales_report

SELECT
  *,
  CASE
  WHEN :p_view_mode = 'Daily'
    THEN DATE_TRUNC('DAY', **YOUR_COLUMN_DATE**)
  ELSE DATE_TRUNC('MONTH', **YOUR_COLUMN_DATE**)
  END AS report_date,
  DATE_FORMAT(**YOUR_COLUMN_DATE**, 'yyyy-MM') AS year_month
FROM 
  **YOUR_TABLE**

Note: Be sure to replace YOUR_TABLE and YOUR_COLUMN_DATE with your actual table and column names.

Step 3: Add the view mode filter to the canvas

Add the view mode filter to the canvas and connect both the dropdown list and the parameter:

• Filter Type: Single Value
• Fields: Select "view_mode" from "options_view_mode"
• Parameters: Select "p_view_mode" from "dataset_sales_report"
• Default Value: "Monthly"

Step 4: Add the Month Filter

To let users filter the data down to a specific month. Add another filter widget to the canvas. In the right panel, configure it as follows:

• Filter Type: Multiple values
• Fields: Select year_month from dataset_sales_report
• Title: Month
• Default Value: Select a default month (e.g., 2026-08)

Step 5: Set up the Bar Chart

Add your bar chart to the canvas:

• Dataset: dataset_sales_report
• X-axis: report_date
• Y-axis: SUM(sales_amount)
• Note: Make sure the X-axis Transform is set to None.

Now, you can view a full year of data in Monthly view. When you filter down to just 1 month, simply change the dropdown to Daily to see each day clearly instead of an unhelpful, single huge bar 😆

Hope this helps!