# Create Metrics in Datasets

## Common Metrics use cases

The next step after defining a Dataset is to add Metrics.

We will use the familiar `ecommerce`

dataset to introduce you some of the most common metric use cases:

**Simple aggregation:**Number of orders (all time)**Cross-model aggregation:**Total order value (all time)**Multiple aggregations in one metric:**Average order value (Total order value / orders count) of customers (all time)**Metric with filtering condition:**Average order value in the last 3 months

The final setup of a dataset with metrics will look something like this:

**Final setup**

**Dataset Definition**

`Dataset ecommerce {`

label: 'Ecommerce'

owner: "[email protected]"

data_source_name: 'demodb'

models: [

orders,

order_items,

products,

users,

]

relationships: [

relationship(orders.user_id > users.id, true),

relationship(order_items.order_id > orders.id, true),

relationship(order_items.product_id > products.id, true)

]

// 1. Simple aggregation

metric count_orders {

label: 'Count Orders'

type: 'number'

definition: @aql count(orders.id) ;;

}

// 2. Cross-model aggregation:

metric sum_order_value {

label: 'Sum Order Values'

type: 'number'

definition: @aql sum(order_items, order_items.quantity * products.price) ;;

}

// 3. Multiple aggregation

metric average_order_value {

label: 'Average Order Value'

type: 'number'

definition: @aql sum_order_value / count_orders;;

}

// 4. Metric with condition:

metric aov_last3m {

label: 'AOV Last 3 Months'

type: 'number'

definition: @aql average_order_value | where(orders.created_at matches @(last 3 months)) ;;

}

}

**Model Relationships**

## Components of a Metric

A basic Metric in Holistics **is required to have** the following components:

- The
**table**that we will run the aggregation on - The
**field**to be aggregated - The
**aggregation function**

These three components are combined into an **aggregation expression**. All of the following syntax are equivalent:

`// Full form`

aggregate_function(table, table.field)

// Shorthand form

aggregate_function(table.field)

// With pipe operator

table | aggregate_function(table.field)

- Please refer to
**Aggregate Functions**document for more details on different forms of aggregation functions. - Please refer to
**Table Expression**document for more details on how to construct table expressions. In this guide, we will work with a simple table expression consisting of**only a model name**. - The
`|`

symbol is the**pipe operator**that take the result of the expression on the left and pass it to the expression on the right.

## Create Metrics

### 1. Simple aggregation

The most basic metric in Holistics involves aggregating a single field in a single model. In this case, we simply `count`

the values of `id`

field in the `orders`

model:

`// 1. Simple aggregation`

metric count_orders {

label: 'Count Orders'

type: 'number'

definition: @aql count(orders.id) ;;

}

### 2. Cross-model aggregation

Here we want to calculate the **value of orders**, which equals to **quantity of items in the order** x **the item's price**. The definition is now a bit more complicated:

`metric sum_order_value {`

label: 'Sum Order Values'

type: 'number'

definition: @aql sum(order_items, order_items.quantity * products.price) ;;

// This is also valid: order_items | sum(order_items.quantity * products.price)

}

In this metrics, there are two tables involved: `order_items`

and `products`

. To let Holistics know exactly from **which table should we run the aggregation on**, we have to explicitly specify it. In this case, it is the `order_items`

table.

This AQL expression equals to the following SQL:

`SELECT`

SUM(order_items.quantity * products.price) AS sum_order_value

FROM order_items

LEFT JOIN products on order_items.product_id = products.id

There are two specific requirements to make cross-model aggregation works correctly:

**Aggregation table must be specified:**If it is not specified in the metric definition like so:`definition: @aql sum(order_items.quantity * products.price) ;;`

you will encounter an

**Invalid expression**error (that basically says the Holistics engine cannot know which table to aggregate on)**Aggregation table must be the N-table in an N - 1 relationship:**For example, the relationship between`order_items`

and`products`

is N - 1. If you specify`products`

as the aggregation table like so:`definition: @aql sum(products, order_items.quantity * products.price) ;;`

you will encounter a

**fan-out error**.

This is a very simple example of cross-model calculation. For more details about this feature, please check out the dedicated Cross-model Calculation doc.

### 3. Multiple aggregations in one Metric

To calculate **Average Order Value (AOV)**, we need to divide **total order value** by **number of orders**. In other words, AOV is an aggregation that involves **two other aggregations**.

Division is a simple arithmetic operation. We can combine the two aggregations like so:

`metric average_order_value {`

label: 'Average Order Value'

type: 'number'

definition: @aql sum(order_items, order_items.quantity * products.price)

/ count(orders, orders.id);;

}

However, we can **reuse** the definition of those two metrics by simply referring their names:

`metric average_order_value {`

label: 'Average Order Value'

type: 'number'

definition: @aql sum_order_value / count_orders;;

}

### 4. Metric with filtering condition

To calculate a metric only on a subset of records, like AOV of orders in the last 3 months, we append a condition to the metric:

`metric aov_last3m {`

label: 'AOV Last 3 Months'

type: 'number'

definition: @aql average_order_value | where(orders.created_at matches @(last 3 months)) ;;

}

Useful documents that you should check out:

**where**: For more details on condition functions**AQL Operators**: List of logical operators that you can use in the conditions**AQL Operators - Datetime section**: How to write datetime filtering conditions

## Using Metrics

After everything is done, if you go to the **Data** tab to preview your dataset, you will see a new **Metrics** section on the left panel with all of the metrics you have created.

## More advanced Metrics use cases

Metrics in Holistics is a powerful tool that allows complex calculations. Here are some topics that may interest you once you have mastered the basics of Metrics: