Semi Additive Measures

Modeling
1

Sometimes, your data is structured in such a way that you can’t just add up all the values to get the total. Examples of this are a daily table of bank balances, your company’s ARR, or the number of items you have in inventory. To aggregate the daily data to monthly or quarterly, you can’t just sum up the daily data. Instead, you need to get the last row of data for that period.

This seems straightforward conceptually, but building a model that can allow seamless self-service on this kind of data can be quite tricky. You could create a filter is_end_of_month and use that for monthly aggregations, but what if you change the aggregation to quarterly? Now you (and your business users) need to change the filter you’re using to is_end_of_quarter to ensure you get the correct results.

This is where Omni’s native semi-additive measures come to the rescue. This allows you to define the date field which should be used to identify the “end of the period”. In your aggregates, you can then filter for where this is true, which means that Omni will only sum data for the last date in the period. This is totally dynamic, so it will seamlessly work whether your period is daily, quarterly, yearly, etc.

Check it out in action here: Semi Additive Measure | Loom

This can be done through the UI, but if you prefer working in the code, the relevant bits of code are:

dimensions:
  last_date:
    hidden: true
    sql: omni_dimensionalize(max(${created_at}))

  is_last_date:
    hidden: true
    sql: ${created_at[date]}=${last_date[date]}
measures:
  semi_additive_sum:
    sql: ${sale_price}
    aggregate_type: sum
    filters:
      is_last_date:
        is: true
4 Likes