Launched: Change Dimension/Metric via Dashboard Filter (by using AQL and Query Parameters)

Product Releases
,
1

Introduction

AQL is getting an upgrade! No more static calculations. You can now make AQL dynamic by combining it with Query Parameters.

This means AQL definitions can adjust based on user inputs via the Dashboard Filter, and you’ll be able to do Dynamic fields selection (dynamic dimensions, dynamic metrics)

Demo

Dynamic Dimension selection

How to set up

In the video, you want your metric to be dynamically broken down by Countries, Cities, and Genders

First, create a Query Parameter in the model. Then, use AQL to create a Dimension that dynamically switches based on the user’s selection through a Dashboard filter.

Model dynamic_model {
  
  dimension country_name { }
  dimension city_name { }
  dimension gender { }
  
  param dim_choice {
    label: 'Dim Choice'
    type: 'text'
    allowed_values: ['Countries', 'Cities', 'Gender']
  }

  dimension dynamic_dim {
    label: 'Dynamic Dim'
    type: 'text'
    definition: @aql case(
      when: 'Countries' in dynamic_dim_model.dim_choice
      , then: dynamic_dim_model.country_name
      , when: 'Cities' in dynamic_dim_model.dim_choice
      , then: dynamic_dim_model.city_name
      , when: 'Gender' in dynamic_dim_model.dim_choice
      , then: dynamic_dim_model.gender
    ) ;;
  }
}

Dynamic Metrics selection

How to set up

Similar to dynamic dimension selection, you can also use Query Parameters to switch the metrics displayed in the visualization.


Model param_model { 
  param metric_selection {
    label: 'Metric Selection'
    type: 'text'
    allowed_values: ['Revenue', 'Total Orders', 'Total Users']
  }
}

Dataset ecommerce {

  models: [ your_models, param_model ]
  relationships: [ ]

  metric revenue {
    label: 'Revenue'
    type: 'number'
    definition: @aql sum(order_items, order_items.quantity * products.price) ;;
  }

  metric total_orders {
    label: 'Total Orders'
    type: 'number'
    definition: @aql count(orders.id) ;;
  }

  metric total_users {
    label: 'Total Users'
    type: 'number'
    definition: @aql count(users.id) ;;
  }

  metric dynamic_metric {
    label: 'Dynamic Metric'
    type: 'number'
    definition: @aql
      case(
        when: 'Revenue' in param_model.metric_selection
        , then: revenue
        , when: 'Total Orders' in param_model.metric_selection
        , then: total_orders
        , when: 'Total Users' in param_model.metric_selection
        , then: total_users
      )
     ;;
  }
}
4 Likes