AQL Condition

Introduction

The AQL Filter Expression feature is designed to enhance the querying capabilities of your analytics environment.

It provides you with the flexibility to apply sophisticated filtering logic, enriching your ability to obtain precise insights from complex datasets.

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:

  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.