Perform Semi-additive Calculation

Knowledge Checkpoint

A grasp of these concepts will help you understand this documentation better:

• Cross-Model Reference
• aggregator-functions
• where
• of_all
• unique
• select

Introduction

Any calculations can be either Additive, Semi-additive, or Non-additive.

• Additive Metrics: are metrics that can be aggregated (or summed) across all the Dimensions that are use with it. For example, the Total Order Items of a Year is the SUM of Total Order Items of each individual Months in that Year.
• Non-additive Metrics: are metrics that cannot be aggregated (or summed) across any of the dimensions. For example, the Distinct Count of Products over a year is not the Sum of the Distinct Count Products of Each Month in that Year.
• Semi-additive Metrics: are metrics can be aggregated across some dimensions, but not all dimensions. A typical example that can be referred in the Example below

Example: Bank Balance

For our example, think of a model that tracks bank balances. If you add up everyone's balance, that's the total amount for all users.

But when considering time, it's not as straightforward. Say we're looking at a quarter: the balance isn't just adding up each month's amount. Instead, we go by the last recorded balance for that quarter.

So, adding up balances depends on what we focus on: time or other factors. This is why we call it 'semi-additive': it can be both additive and non-additive, depending on the situation.

As you can see, a simple SUM to aggregate balances returns the wrong result both at the quarter and at the year levels:

The total at the quarter level cannot be computed by summing the individual months. Instead, the formula must only consider the last value.

Possible approaches

One of the solution is to search for the Last Date of the Balance Date and compare it with the Date used in the Dimension

metric sum_bank_balance {
    label: 'Sum Bank Balance'
    type: 'number'
    definition: @aql sum(balances.bank_balance) 
      | where(dim_dates.date == max(balances.record_date)) ;;
    }

You will get the below result, the Total Balance of Quarter is the Total Balance of the last Month in that Quarter, instead of summing all the Months in that Quarter

However, you might notice that the Total Balance of all Customers looks wrong. It doesn’t take the sum balance of all Customers.

To investigate this, you might want to use the actual balance date of each customer.

The last balance dates for Hehr Elliott, Molleur Florance, and Francis Kub are January 26th, January 20th, and January 28th. When AQL looks at the latest date for the final two customers, it shows January 26th and January 20th. For Francis Kub, the latest date is January 28th.

If no specific customer is selected, the latest date becomes the last date for all customers, which is January 28th. But there's no data for Hehr Elliott and Molleur Florance on this day.

So, based on what your company needs, you might want the last balance day for each customer or the last balance day for all of them.

Calculate the Last Balance Day for All Customers

If you want to calculate the Last day for the All Customers, you may want to use the of_all() function in your calculation.

metric sum_bank_balance_any_customers {
    label: 'Sum Bank Balance Any Customers'
    type: 'number'
    definition: @aql 
      sum(balances.bank_balance) | where(dim_dates.date == (
          max(balances.record_date) | of_all(balances.user_name)  
      )) ;;
    }

You will get the result like below

Calculate Last Balance Day for each Customer.

The other Solution is to calculate the Last Balance Day for each individual customer, and then sum the number together.

You can leverage the function unique() to calculate the Last Balance day by each Customer

metric sum_bank_balance_each_customers {
    label: 'Sum Bank Balance Each Customers'
    type: 'number'
    definition: @aql 
      unique(balances.user_name)
        | select(
            user_name: balances.user_name, 
            sum_balance: (sum(balances.bank_balance) | where(dim_dates.date == max(balances.record_date)))  
            )
        | sum(sum_balance)
     ;;
  }

You will get the result like below