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

Hey folks!

We’ve got exciting news to share with you - we have been working on a brand new Analytics Query Language, AQL! :rocket:

AQL Expression is the next-gen upgrade to our current Business Calculation mechanism, designed to give you a more powerful and flexible way of querying data for analytics purposes.

Use Cases:

With AQL Expression, you can do things like:

  • Cross model calculation - Perform calculations across multiple models without having to manually merge the data via SQL Model.
  • Nested Aggregation - Create more complex calculations by nesting functions and aggregations within each other.
  • Add filtering in an AQL Expression - Filter your data at the query level rather than having to do it in a separate step.
  • Support Date/Time Intelligence Functions - Analyze your data over time using powerful date/time intelligence functions.
  • Subgroup Calculation - Perform calculations on subgroups of your data without having to create a separate query for each subgroup.
  • And so much more!

We’re confident that AQL will make your analytics workflow faster, more efficient, and more intuitive than ever before.

For more information on how to use it, please refer to our doc here


Please be aware that: This feature is currently only supported for version Holistics 4.0

5 Likes

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

Sub-group Calculation

With AQL Expression, you can perform Subgroup Calculation flexibly and easily without having to create SQL (or transform) Model.

You can refer to the video below for more information.

Sample syntax

sum(order_items.quantity) * 1.0 /
  eval(sum(order_items.quantity), exclude(users.age_group))

If you have any questions in the meantime, please don’t hesitate to ask.

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

Cross-model Calculation

Let’s say you are a Country Manager of an Ecommerce Company and you want to calculate the Revenue by Country.

The formula to calculate it is the sum of the product price (from the Product model) multiplied by the quantity (from the Order Items model).

order_items | sum(order_items.quantity * products.price)

They are from 2 different models so you can write an AQL expression like the video below:

1 Like

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.

2 Likes

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

2 Likes

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

Hi,

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.

Best,

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:

sum(products.price) 
	| 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.

3 Likes

Hi,

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.

5 Likes

: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,
Damien

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