[🚀 Launched] Our brand new Analytics Query Language, AQL!

I’d be interested in beta testing for this. I’d be particularly interested in checking out time-based and cross-model functions - but tbh I’d just like to see how far this might go to reduce our use of custom dimensions/measures to get around the limitations in business calculations and expressions.


We would love to be part of the Beta phase of this, so please sign us up.


Please sign us up! Our organization would really benefit from cross model calculations, nested aggregations, and date/time functions as we currently use workarounds in transform models instead.

1 Like

Hi, this is definitely the best holistics news of the month :slightly_smiling_face:
We would very much like to test the beta, only if we can be sure that it will not break our current environment :wink:

1 Like

We are very interested in being part of the beta phase for this feature!

1 Like

Do note that we are currently in the development phase, exploring various solutions, which may result in changes to syntax and UI in the future

Use Aggregation inside a Dimension

We will also support using aggregation as part of a dimension. This will enable use cases like binning and Cohort (as you can see in the demo video below).

Let’s say you are working for an Ecommerce Company and you want to find the Acquisition Cohort on a yearly basis.

To explain, Acquisition Cohort refers to a group of users who were acquired (signed up or made their first purchase) during a specific time period. This cohort is often analyzed to understand customer behavior, retention, and lifetime value.

Enroll in Beta

If you’re interested in participating in the beta, please let us know by filling our beta request form


What happens if a filter is set in the dashboard on users.age_group ? Will this function exclude the filter as well ?

1 Like

Short answer is YES. The Divisor eval(sum(order_items.quantity), exclude(users.age_group)) will basically exclude (or skip) all the filtering from the dimension age_group.

Do note that the syntax could be modified in the future but the idea is the same.

1 Like

Hi, one last question before enrolling in beta : is the new AQL language backward-compatible with the current language ?

Hi @dacou,

Currently, if you utilize aggregate functions such as sum(field) or count(field), these will be compatible with AQL.
However, non-aggregate functions in business calculations (e.g., case...when..., and(), or(), etc.) are not yet compatible. Our engineering team is diligently working on a compatibility mode to ensure that your existing Business Calculations function seamlessly.

Transitioning from the current Business Calculation framework to AQL represents a significant architectural change. Our primary objective is to guarantee the compatibility of your existing Business Calculations to our AQL system. We will release an official announcement for Business Calculations and AQL soon.


Hello everyone, our AMQL team has exciting news to share.

We are pleased to introduce a streamlined method for calculating Running Totals figures.

For running totals, you can simply use this function:

count(orders.id) | running_total()

Additionally, we now offer the ability to calculate Period To Date:

	| period_to_date("year", order_items.created_at | month())

These features represent just a fraction of the capabilities of AQL expressions. There’s much more to come!

Feel free to reach out if you have any questions.



I understand it is possible with AQL to “dimensionalize” a measure as described in the documentation for exact_grains. But I’m trying to do ti on the reporting side with no luck for now. Is there a way to do this ?

It would be helpful for end users to create there own custom dimensions (buckets, ranges, …). Or for the scatter chart that often requires to pre-aggregate the data

1 Like

Hi @dacou,
Yes we’re aware of the needs to define Adhoc AQL Dimension on Reporting side and it indeed makes sense.
Do note that it’s already included in our backlog for future iteration, for now, to define AQL Dimension, you would need to define it in the Modeling layer.

1 Like

Hi everyone, our AMQL team is back with new updates.

We’re glad to announce that we have officially supported function to calculate Period-over-Period (or PoP).

With our new PoP function, you should be able to

  • Calculate metrics of the same period last years/months/weeks (or even last 2 years, 3 years, etc.)
  • Calculate the metrics of the same period but next year (mostly for cases where you have forecasted sales data or you just want calculate the metrics for next year [relative to the current period])
  • Calculate the Delta Δ or % Changes between 2 periods

You could refer to the sample syntax below

// Relative Period

// Count orders.id 1 month ago (relative to the current period)
count(orders.id) | relative_period(orders.created_at, interval(-1 month))

// Count orders.id 1 year ago (relative to the current period)
count(orders.id) | relative_period(orders.created_at, interval(1 month))

// Count orders.id next year (relative to the current period)
count(orders.id) | relative_period(orders.created_at, interval(1 year))

//  Exact Period

// Count orders.id in 2022
count(orders.id) | exact_period(orders.created_at, @2022)

// Count orders.id from 2023-04-08 to 2023-09-10
count(orders.id) | exact_period(orders.created_at, @2020-04-08 - 2021-09-10)

Our team is writing a public document to further guide you on how to leverage these functions. We will send our updates here.

Feel free to reach out if you have any questions.


:rocket: AQL has officially supported date_diff function

We’re glad to announce that we have supported a more convenient way for you all to find the duration between 2 date fields.

Let’s imagine you’re working for an ecommerce company and you want to find out How long a user hasn’t placed an order, you would need to subtract 2 dates (Current Time and the Last time that Customer made the Order).

You just need to simply use the date_diff() function for that types of calculation

date_diff('day', max(orders.created_at), @(now))

With this function, you can find the duration between any 2 date fields

date_diff(<Interval>, <Date 1>, <Date 2>)

Feel free to reach out if you have any questions.

1 Like

I just noticed that I was using the keyword metric in datasets and measure in models :face_with_monocle:. I did not find a doc explaining the difference.

Here, the documentation defines what a metric is and takes a measure as an example :wink:

Should both terms be synonyms ?

Best regards,

Hi @dacou,
We have a section right here to explain the difference between these 2 concepts

You can refer to our doc for more information: Defining AQL | Holistics Docs

1 Like

Hi @dacou

We have officially supported creating adhoc AQL Dimension in the Reporting side.

Do let me know if you have any questions :smile:


This is an interesting use case for a problem i’m having. When you add Country as a dimention to aggregate by - the executed query is showing that you start with order item and then left join country by an id. What happens then in a scenario where a country exists in the country table but doesn’t have an order? From that executed query you would be missing a data point saying that there are 0 revenue. How would you build the AQL / relationships to ensure you see that data point?

Hi @nicholashernandezjlo

Thanks for raising the concerns. To explain why Holistics choose such joining strategy, there are 2 reasons:

  1. It prevents fan-out/double aggregation on measures.
  2. It avoids data anomalies where the data doesn’t conform to the relationship. For example: Given that we have orders > country (n-1 relationship). Then, orders without country info will show up with a null country column when you aggregate them by country.

What happens then in a scenario where a country exists in the country table but doesn’t have an order? From that executed query you would be missing a data point saying that there are 0 revenue.

Your concern is indeed valid. As for now, If you wish to see the countries that are having 0 revenue, you could workaround by adding count(countries.id) field into your visualization.

How would you build the AQL / relationships to ensure you see that data point?

As for now, AQL does not provide such utility but we would definitely consider it carefully.

Please don’t hesitate to reach out to us if you have further concerns.