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 refer to our Dynamic Conditions documentation.