AQL Condition

Introduction

AQL Condition is an expression that lets you apply complex criteria to all metrics in your explore. It follows the structure of the condition parameter in the where function.

Here's an example of an explore with AQL metrics:

explore {
  dimensions {
      // your_dimensions
  }
  measures {
    orders: total_orders | where(users.gender == 'female' or countries.name == 'Vietnam'),
    revenue: revenue | where(users.gender == 'female' or countries.name == 'Vietnam')  ,
    users: total_users | where(users.gender == 'female' or countries.name == 'Vietnam')
  }
  filters {
    // your_condition
  }
}

With AQL Condition, you can streamline the explore like this:

explore {
  dimensions {
      // your_dimensions
  }
  measures {
    orders: total_orders,
    revenue: revenue,
    users: total_users
  }
  filters {
    users.gender == 'female' or countries.name == 'Vietnam'
  }
}

This approach lets you easily apply advanced filtering to all metrics, helping you get accurate insights from complex data.

For example, you will be able to do:

• Nested Filtering
• Filter multiple fields from different models simultaneously

How to use

1. Go to the Visualization section.
2. Navigate to the Condition tab.
3. Click on "Add AQL Condition"

Sample use cases

Example 1: Nested Filtering

Scenario: You want to find a list of customers who made their first purchase in a specific category (e.g., the gaming category). From this list, you want to understand how many products these customers purchased.

How to Solve This:

1. Obtain a metric that returns the list of customers who made their first purchase in the "Gaming" category.

   // metric name: first_gaming_users
   
   unique(users.name, orders.id, categories.name) 
     | select(
       user_name: users.name,
       order_id: orders.id,
       category_name: categories.name,
       _rank: rank(
         order: min(orders.created_at) | of_all(categories.name),
         partition: users.name
       )
     )
     | filter(_rank == 1, category_name == 'Gaming')
     | select(user_name)

   

2. Build a visualization showing the total products bought by each user, filtered to the list of users identified in step 1.

   

Example 2: Filter multiple fields from different models

Scenario: You have a dataset that contains transaction details between buyers and sellers. There is a field called "gender" in both the buyers and sellers models. You want to simultaneously apply the filter to both gender fields to understand the transaction details between male or female buyers and sellers.

Without filter expression, you would have to apply filter for buyer_gender and seller_gender individually

Write an AQL Filter Expression to answer this question:

buyers.buyer_gender == 'male'
and 
sellers.seller_gender == 'male'

And if you want your users to apply filter for the Gender of both Buyers and Sellers via Dashboard Filter, you can set up Query Parameter and use it inside AQL Filter Expression (like below)

• (1) Create a Query Model to store your parameters:

  Model param_model {
      label: 'Param Model'
      type: 'query'
      data_source_name: 'your_db'
      query: @sql select 1 ;;
      
      param gender_param {
          label: 'Gender Param'
          type: 'text'
      }
  
  }
• (2) Add the Query Model to Your Dataset:

  Dataset your_dataset {
      label: 'Your Dataset'
      data_source_name: 'your_db'
      models: [model_1, model_2, param_model]
  }
• (3) Edit the Dashboard Widget that you want to apply the condition
  • Create a new Filter Expression in the Condition:

    buyers.buyer_gender == (param_model.gender_param | first())
    and 
    sellers.seller_gender == (param_model.gender_param | first())
• (4) Set Up Dashboard Filter:
  • Create a field filter called "Gender".
  • Set its source to the any Gender field from Buyers or Sellers model.
  • Map this filter to gender_param.
  • Configure the filter to be single-select.