We’ve got exciting news to share with you - we have been working on a brand new Analytics Query Language, AQL!
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
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.
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.
Hi, this is definitely the best holistics news of the month
We would very much like to test the beta, only if we can be sure that it will not break our current environment
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
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.
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.
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
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.
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))
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
. I did not find a doc explaining the difference.