# \[šŸš€ Launched\] Our brand new Analytics Query Language, AQL! **URL:** \ **Category:** Product Releases\ **Tags:** aml\ **Created:** [June 1, 2023, 8:14am UTC](https://community.holistics.io/t/launched-our-brand-new-analytics-query-language-aql/1533 "2023-06-01T08:14:10Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Khai\_To](https://community.holistics.io/user_avatar/community.holistics.io/khai_to/32/128_2.png) [@Khai\_To](https://community.holistics.io/u/Khai_To)\ **Post date:** [June 1, 2023, 8:14am UTC](https://community.holistics.io/t/launched-our-brand-new-analytics-query-language-aql/1533/1 "2023-06-01T08:14:10Z")
Hey folks! 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](https://docs.holistics.io/as-code/amql/aql-learn-in-30-minutes)** **Please be aware that** : This feature is currently only supported for version Holistics 4.0 ---
**Author:** ![Khai\_To](https://community.holistics.io/user_avatar/community.holistics.io/khai_to/32/128_2.png) [@Khai\_To](https://community.holistics.io/u/Khai_To)\ **Post date:** [June 1, 2023, 8:15am UTC](https://community.holistics.io/t/launched-our-brand-new-analytics-query-language-aql/1533/2 "2023-06-01T08:15:35Z")
_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. [https://cdn.holistics.io/community/amql/aql\_demo\_subgroup\_calculation.mp4](https://cdn.holistics.io/community/amql/aql_demo_subgroup_calculation.mp4) Sample syntax ```js 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. ---
**Author:** ![Khai\_To](https://community.holistics.io/user_avatar/community.holistics.io/khai_to/32/128_2.png) [@Khai\_To](https://community.holistics.io/u/Khai_To)\ **Post date:** [June 1, 2023, 8:53am UTC](https://community.holistics.io/t/launched-our-brand-new-analytics-query-language-aql/1533/3 "2023-06-01T08:53:12Z")
_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). ```ts 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: [https://cdn.holistics.io/community/amql/demo-aql-cross-calc.mp4](https://cdn.holistics.io/community/amql/demo-aql-cross-calc.mp4) ---
**Author:** ![Ian\_McLaren](https://community.holistics.io/user_avatar/community.holistics.io/ian_mclaren/32/647_2.png) [@Ian\_McLaren](https://community.holistics.io/u/Ian_McLaren)\ **Post date:** [June 1, 2023, 9:45am UTC](https://community.holistics.io/t/launched-our-brand-new-analytics-query-language-aql/1533/4 "2023-06-01T09:45:05Z")
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. ---
**Author:** ![DataGeekDude](https://community.holistics.io/user_avatar/community.holistics.io/datageekdude/32/430_2.png) [@DataGeekDude](https://community.holistics.io/u/DataGeekDude)\ **Post date:** [June 1, 2023, 12:13pm UTC](https://community.holistics.io/t/launched-our-brand-new-analytics-query-language-aql/1533/5 "2023-06-01T12:13:49Z")
We would love to be part of the Beta phase of this, so please sign us up. ---
**Author:** ![tasha\_launchcode](https://community.holistics.io/letter_avatar_proxy/v4/letter/t/5e9695/32.png) [@tasha\_launchcode](https://community.holistics.io/u/tasha_launchcode)\ **Post date:** [June 2, 2023, 10:41pm UTC](https://community.holistics.io/t/launched-our-brand-new-analytics-query-language-aql/1533/6 "2023-06-02T22:41:21Z")
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. ---
**Author:** ![dacou](https://community.holistics.io/user_avatar/community.holistics.io/dacou/32/467_2.png) [@dacou](https://community.holistics.io/u/dacou)\ **Post date:** [June 6, 2023, 8:51am UTC](https://community.holistics.io/t/launched-our-brand-new-analytics-query-language-aql/1533/7 "2023-06-06T08:51:25Z")
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 šŸ˜‰ ---
**Author:** ![bjerrum](https://community.holistics.io/letter_avatar_proxy/v4/letter/b/53a042/32.png) [@bjerrum](https://community.holistics.io/u/bjerrum)\ **Post date:** [June 6, 2023, 8:08pm UTC](https://community.holistics.io/t/launched-our-brand-new-analytics-query-language-aql/1533/8 "2023-06-06T20:08:00Z")
We are very interested in being part of the beta phase for this feature! ---
**Author:** ![Khai\_To](https://community.holistics.io/user_avatar/community.holistics.io/khai_to/32/128_2.png) [@Khai\_To](https://community.holistics.io/u/Khai_To)\ **Post date:** [June 9, 2023, 4:55am UTC](https://community.holistics.io/t/launched-our-brand-new-analytics-query-language-aql/1533/9 "2023-06-09T04:55:09Z")
_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. [https://cdn.holistics.io/community/amql/demo\_aql\_dimensionalized\_measure.mp4](https://cdn.holistics.io/community/amql/demo_aql_dimensionalized_measure.mp4) ## Enroll in Beta If youā€™re interested in participating in the beta, please let us know by filling our **[beta request form](https://form.jotform.com/230511857392457?betaFeature=AQL%20Expression)** ---
**Author:** ![dacou](https://community.holistics.io/user_avatar/community.holistics.io/dacou/32/467_2.png) [@dacou](https://community.holistics.io/u/dacou)\ **Post date:** [June 13, 2023, 2:25pm UTC](https://community.holistics.io/t/launched-our-brand-new-analytics-query-language-aql/1533/10 "2023-06-13T14:25:45Z")
Hi, > [@Khai\_To](#): > > ```auto > sum(order_items.quantity) * 1.0 / > eval(sum(order_items.quantity), exclude(users.age_group)) > > ``` What happens if a filter is set in the dashboard on `users.age_group` ? Will this function exclude the filter as well ? ---
**Author:** ![Khai\_To](https://community.holistics.io/user_avatar/community.holistics.io/khai_to/32/128_2.png) [@Khai\_To](https://community.holistics.io/u/Khai_To)\ **Post date:** [June 14, 2023, 5:32am UTC](https://community.holistics.io/t/launched-our-brand-new-analytics-query-language-aql/1533/11 "2023-06-14T05:32:34Z")
> [@dacou](#): > > What happens if a filter is set in the dashboard on `users.age_group` ? Will this function exclude the filter as well ? 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. ---
**Author:** ![dacou](https://community.holistics.io/user_avatar/community.holistics.io/dacou/32/467_2.png) [@dacou](https://community.holistics.io/u/dacou)\ **Post date:** [June 21, 2023, 9:21pm UTC](https://community.holistics.io/t/launched-our-brand-new-analytics-query-language-aql/1533/12 "2023-06-21T21:21:00Z")
Hi, one last question before enrolling in beta : is the new AQL language backward-compatible with the current language ? ---
**Author:** ![Khai\_To](https://community.holistics.io/user_avatar/community.holistics.io/khai_to/32/128_2.png) [@Khai\_To](https://community.holistics.io/u/Khai_To)\ **Post date:** [June 23, 2023, 4:08am UTC](https://community.holistics.io/t/launched-our-brand-new-analytics-query-language-aql/1533/13 "2023-06-23T04:08:59Z")
> [@dacou](#): > > 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, ---
**Author:** ![Khai\_To](https://community.holistics.io/user_avatar/community.holistics.io/khai_to/32/128_2.png) [@Khai\_To](https://community.holistics.io/u/Khai_To)\ **Post date:** [October 16, 2023, 10:22am UTC](https://community.holistics.io/t/launched-our-brand-new-analytics-query-language-aql/1533/14 "2023-10-16T10:22:51Z")
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: ```ts count(orders.id) | running_total() ``` [https://cdn.holistics.io/community/amql/running-total/demo\_aql\_running\_basic.mp4](https://cdn.holistics.io/community/amql/running-total/demo_aql_running_basic.mp4) Additionally, we now offer the ability to calculate Period To Date: ```ts sum(products.price) | period_to_date("year", order_items.created_at | month()) ``` ![image](https://community.holistics.io/uploads/default/original/2X/3/3b011481b43a1683d326a32794d2c81c7565fc66.png) 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. ---
**Author:** ![dacou](https://community.holistics.io/user_avatar/community.holistics.io/dacou/32/467_2.png) [@dacou](https://community.holistics.io/u/dacou)\ **Post date:** [October 17, 2023, 4:46pm UTC](https://community.holistics.io/t/launched-our-brand-new-analytics-query-language-aql/1533/15 "2023-10-17T16:46:14Z")
Hi, I understand it is possible with AQL to ā€œdimensionalizeā€ a measure as described in the [documentation for exact\_grains](https://docs.holistics.io/as-code/reference/macros/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 ---
**Author:** ![Khai\_To](https://community.holistics.io/user_avatar/community.holistics.io/khai_to/32/128_2.png) [@Khai\_To](https://community.holistics.io/u/Khai_To)\ **Post date:** [October 18, 2023, 7:31am UTC](https://community.holistics.io/t/launched-our-brand-new-analytics-query-language-aql/1533/16 "2023-10-18T07:31:53Z")
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. ---
**Author:** ![Khai\_To](https://community.holistics.io/user_avatar/community.holistics.io/khai_to/32/128_2.png) [@Khai\_To](https://community.holistics.io/u/Khai_To)\ **Post date:** [November 8, 2023, 7:21am UTC](https://community.holistics.io/t/launched-our-brand-new-analytics-query-language-aql/1533/17 "2023-11-08T07:21:14Z")
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 [https://cdn.holistics.io/community/amql/pop/aql\_demo\_PoP.mp4](https://cdn.holistics.io/community/amql/pop/aql_demo_PoP.mp4) You could refer to the sample syntax below ```ts // 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)) ``` ![image](https://community.holistics.io/uploads/default/original/2X/e/e8469e33676008a26996ed8a004231b6e6bae9c8.png) * * * ```ts // 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) ``` ![image](https://community.holistics.io/uploads/default/original/2X/7/79236e6b77d740db9ac24af1197b03ead2991d39.png) 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. ---
**Author:** ![Khai\_To](https://community.holistics.io/user_avatar/community.holistics.io/khai_to/32/128_2.png) [@Khai\_To](https://community.holistics.io/u/Khai_To)\ **Post date:** [December 26, 2023, 6:32am UTC](https://community.holistics.io/t/launched-our-brand-new-analytics-query-language-aql/1533/18 "2023-12-26T06:32:54Z")
## šŸš€ 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). [https://cdn.holistics.io/community/amql/aql\_demo\_date\_diff.mp4](https://cdn.holistics.io/community/amql/aql_demo_date_diff.mp4) You just need to simply use the `date_diff()` function for that types of calculation ```ts date_diff('day', max(orders.created_at), @(now)) ``` With this function, you can find the duration between any 2 date fields ```ts date_diff(, , ) ``` Feel free to reach out if you have any questions. ---
**Author:** ![dacou](https://community.holistics.io/user_avatar/community.holistics.io/dacou/32/467_2.png) [@dacou](https://community.holistics.io/u/dacou)\ **Post date:** [December 27, 2023, 1:53pm UTC](https://community.holistics.io/t/launched-our-brand-new-analytics-query-language-aql/1533/19 "2023-12-27T13:53:39Z")
I just noticed that I was using the keyword `metric` in datasets and `measure` in models šŸ§. I did not find a doc explaining the difference. [Here](https://docs.holistics.io/as-code/amql/aql-learn-in-30-minutes#what-is-a-metric), the documentation defines what a metric is and takes a measure as an example šŸ˜‰ Should both terms be synonyms ? Best regards, Damien ---
**Author:** ![Khai\_To](https://community.holistics.io/user_avatar/community.holistics.io/khai_to/32/128_2.png) [@Khai\_To](https://community.holistics.io/u/Khai_To)\ **Post date:** [December 29, 2023, 9:13am UTC](https://community.holistics.io/t/launched-our-brand-new-analytics-query-language-aql/1533/20 "2023-12-29T09:13:26Z")
> [@dacou](#): > > I just noticed that I was using the keyword `metric` in datasets and `measure` in models šŸ§. I did not find a doc explaining the difference. Hi @dacou, We have a section right here to explain the difference between these 2 concepts ![image](https://community.holistics.io/uploads/default/original/2X/e/e7635e4d4f88d6ef73f1446722850e6c5ade9671.png) You can refer to our doc for more information: [Defining AQL | Holistics Docs](https://docs.holistics.io/as-code/amql/defining-aql#aql-metrics) [Next page](https://community.holistics.io/t/launched-our-brand-new-analytics-query-language-aql/1533.md?page=2)