# Multi-Period KPI Summary

> Build report showing key business metrics (like GMV, AOV, and Total Orders) across time windows like Week, MTD, and YTD: all in one summary table.

## Introduction

Stakeholders often want a single summary view showing key **business metrics side-by-side across several time windows**, much like an executive scorecard or a KPI summary sheet in a spreadsheet. 

For example, you might want to see GMV, NMV, AOV, total orders, delivered orders, cancelled orders, and cancellation rate for the current week, month-to-date (MTD), MTD last year, year-to-date (YTD), and YTD last year, all in one compact table.

This pattern works for any type of metric, whether it's a monetary sum like GMV, a count like Total Orders, a ratio like AOV, or a percentage like Cancelled Order Ratio.

In this guide, we will walk you through how to build this kind of report in Holistics by combining a standalone query model, conditional metrics, and a [Pivot Table](/docs/charts/pivot-table).

## High-level flow

Here is how the pieces fit together:

1. **Create a Time Type query model.** We build a standalone [query model](/docs/query-models) that generates one row per time period label (for e.g., `Week`, `MTD`, `MTD LY`, `YTD`, `YTD LY`). This model doesn't need any relationships to your fact tables.

2. **Define conditional metrics using `case()`.** For each metric (e.g., GMV), we write a `case()` expression that branches on the Time Type value. Each branch applies `where()` with a [natural time expression](/docs/datetimes/relative-dates) like `@(this week)` or `@(this year to today)`. For "last year" variants, we also apply `relative_period()` to shift the time window back by one year.

3. **Visualize in a Pivot Table.** We place the Time Type dimension in the **Columns** shelf and the metrics as **Values** (displayed as rows). This gives us the familiar spreadsheet-style layout.

## Setup

For this guide, we use an `ecommerce` dataset with `orders`, `order_items`, `products`, `users`, and a `date_dim`. We also define several base metrics that we will later wrap with time-period logic.

```aml title="ecommerce.dataset.aml"
Dataset ecommerce {
  models: [
    orders, users, order_items, products, date_dim
  ]

  relationships: [
    relationship(orders.user_id > users.id, true),
    relationship(order_items.order_id > orders.id, true),
    relationship(order_items.product_id > products.id, true),
    relationship(orders.created_date > date_dim.date_key, true)
    // No relationship for time_type: it's intentionally standalone
  ]

  // --- Base metrics (these are what we'll wrap with time-period logic) ---

  metric gmv {  }

  metric nmv {  }

  metric total_orders {  }

  metric aov {  }

  metric total_delivered_orders {  }

  metric total_cancelled_orders {  }

  metric cancelled_order_ratio {  }

}
```

With these base metrics in place, we are ready to create the time-period variants.

## Implementation

### Step 1: Create the Time Type query model

The first step is to create a standalone query model **that generates the time period labels**. This model uses a simple SQL `VALUES` clause to produce one row per period. It doesn't need a relationship to your fact tables, as it serves as a virtual dimension table that provides column headers for the pivot.

We prefix each label with a number (e.g., `1. Week`, `2. MTD`) to control the sort order of columns in the final pivot table.

```aml title="time_type.model.aml"
Model time_type {
  type: 'query'
  label: 'Time Type'
  data_source_name: 'your_data_source'

  query: @sql
    SELECT time_type FROM (VALUES
      ('1. Week'),
      ('2. MTD'),
      ('3. MTD LY'),
      ('4. YTD'),
      ('5. YTD LY')
    ) AS t(time_type) ;;

  dimension time_type {
    label: 'Time Type'
    type: 'text'
    definition: @sql {{ #SOURCE.time_type }} ;;
  }
}
```

:::info
The `VALUES` syntax works on most modern databases (PostgreSQL, BigQuery, Snowflake, etc.). If your database doesn't support it, you can use `UNION ALL` instead:

```sql
SELECT '1. Week' AS time_type
UNION ALL SELECT '2. MTD'
UNION ALL SELECT '3. MTD LY'
UNION ALL SELECT '4. YTD'
UNION ALL SELECT '5. YTD LY'
```
:::

### Step 2: Add the Time Type model to the dataset

Once the model is created, add `time_type` to the `models` list of your dataset. You don't need to define any relationship for it

```aml title="ecommerce.dataset.aml"
Dataset ecommerce {
  ...

  models: [
    orders, users, order_items, products, date_dim,
    // highlight-next-line
    time_type  // standalone: no relationship needed
  ]

  relationships: [
    // your existing relationships stay the same
    relationship(orders.user_id > users.id, true),
    relationship(order_items.order_id > orders.id, true),
    ...
  ]
}
```

### Step 3: Define conditional metrics

Now we define metrics that compute different values depending on which time period is active. The `case()` function branches on `time_type.time_type`, and each branch uses `where()` with a [natural time expression](/docs/datetimes/relative-dates) to filter the date range. For "last year" variants, we pipe through `relative_period()` to shift the window back by one year.

Here is what each branch does:

| Branch | Time Expression | Meaning |
|--------|----------------|---------|
| `1. Week` | `@(this week)` | Current calendar week |
| `2. MTD` | `@(this month to today)` | Start of current month through today |
| `3. MTD LY` | `relative_period` + `@(this month to today)` | Same month-to-date window, shifted 1 year back |
| `4. YTD` | `@(this year to today)` | Start of current year through today |
| `5. YTD LY` | `relative_period` + `@(this year to today)` | Same year-to-date window, shifted 1 year back |

Here are two examples (`GMV` and `Total Orders`), showing how the pattern works:

```aml title="ecommerce.dataset.aml"
Dataset ecommerce {
  ...

  metric gmv_time_compare {
      label: 'GMV (time compare)'
      type: 'number'
      definition: @aql case(
          when: time_type.time_type == "1. Week"
          , then: gmv | where(date_dim.date_key match @(this week))
          , when: time_type.time_type == "2. MTD"
          , then: gmv | where(date_dim.date_key match @(this month to today))
          , when: time_type.time_type == "3. MTD LY"
          , then: gmv | relative_period(date_dim.date_key, interval(-1 year)) | where(date_dim.date_key match @(this month to today))
          , when: time_type.time_type == "4. YTD"
          , then: gmv | where(date_dim.date_key match @(this year to today))
          , when: time_type.time_type == "5. YTD LY"
          , then: gmv | relative_period(date_dim.date_key, interval(-1 year)) | where(date_dim.date_key match @(this year to today))
      ) ;;
      format: "[$]#,###0"
  }

  metric total_orders_time_compare {
      label: 'Total Orders (time compare)'
      type: 'number'
      definition: @aql case(
          when: time_type.time_type == "1. Week"
          , then: total_orders | where(date_dim.date_key match @(this week))
          , when: time_type.time_type == "2. MTD"
          , then: total_orders | where(date_dim.date_key match @(this month to today))
          , when: time_type.time_type == "3. MTD LY"
          , then: total_orders | relative_period(date_dim.date_key, interval(-1 year)) | where(date_dim.date_key match @(this month to today))
          , when: time_type.time_type == "4. YTD"
          , then: total_orders | where(date_dim.date_key match @(this year to today))
          , when: time_type.time_type == "5. YTD LY"
          , then: total_orders | relative_period(date_dim.date_key, interval(-1 year)) | where(date_dim.date_key match @(this year to today))
      ) ;;
      format: "#,###"
  }

  // ... same pattern for nmv, aov, total_delivered_orders, cancelled_order_ratio, etc.

}
```

Notice how the two metrics are nearly identical, only the base metric name (`gmv` vs `total_orders`), the label, and the format differ. The `case()` structure and the `relative_period()` / `where()` logic are exactly the same. For "last year" branches, we apply `relative_period(date_dim.date_key, interval(-1 year))` first to shift the time window, then `where()` to filter the date range.

As you add more metrics, this **copy-paste pattern quickly becomes hard to maintain**.

#### Reduce repetition with AML Func

Instead of duplicating this block for every metric, we can use an [AML Function](/reference/aml/func) with [string interpolation](/reference/aml/string-interpolation) to extract the pattern into a reusable function.

Define the function in a separate file (or at the top of your dataset file):

```aml title="time_compare_func.aml"
Func time_compare(metric_name: String, metric_label: String, format: String) {
  Metric {
    label: '${metric_label} (time compare)'
    type: 'number'
    definition: @aql case(
        when: time_type.time_type == "1. Week"
        , then: ${metric_name} | where(date_dim.date_key match @(this week))
        , when: time_type.time_type == "2. MTD"
        , then: ${metric_name} | where(date_dim.date_key match @(this month to today))
        , when: time_type.time_type == "3. MTD LY"
        , then: ${metric_name} | relative_period(date_dim.date_key, interval(-1 year)) | where(date_dim.date_key match @(this month to today))
        , when: time_type.time_type == "4. YTD"
        , then: ${metric_name} | where(date_dim.date_key match @(this year to today))
        , when: time_type.time_type == "5. YTD LY"
        , then: ${metric_name} | relative_period(date_dim.date_key, interval(-1 year)) | where(date_dim.date_key match @(this year to today))
      )  ;;
    format: format
  }
}
```

The function takes three parameters:
- `metric_name`: the name of the base metric to wrap (e.g., `'gmv'`, `'total_orders'`)
- `metric_label`: the display label (e.g., `'GMV'`, `'Total Orders'`)
- `format`: the number format string

Inside the function, `${metric_name}` and `${metric_label}` are injected into the metric definition via [string interpolation](/reference/aml/string-interpolation).

Now each metric declaration becomes a single line:

```aml title="ecommerce.dataset.aml"
Dataset ecommerce {
  ...

  // time comparison metrics
  metric gmv_time_compare: time_compare('gmv', 'GMV', '[$]#,###0')
  metric nmv_time_compare: time_compare('nmv', 'NMV', '[$]#,###0')
  metric aov_time_compare: time_compare('aov', 'AOV', '[$]#,###0')
  metric total_orders_time_compare: time_compare('total_orders', 'Total Orders', '#,###')
  metric total_delivered_orders_time_compare: time_compare('total_delivered_orders', 'Total Delivered Orders', '#,###')
  metric total_cancelled_orders_time_compare: time_compare('total_cancelled_orders', 'Total Cancelled Orders', '#,###')
  metric cancelled_order_ratio_time_compare: time_compare('cancelled_order_ratio', 'Cancelled Order Ratio', '#,###0.00%')
}
```

Adding a new metric to the scorecard is now a one-liner, no more copy-pasting 15 lines of `case()` logic.

### Step 4: Build the pivot table

With the model and metrics in place, create an exploration and set up the visualization:

1. Add `time_type.time_type` to the **Columns** shelf.
2. Add your time-compare metrics (e.g., `gmv_time_compare`, `nmv_time_compare`, `aov_time_compare`, `total_orders_time_compare`, `cancelled_order_ratio_time_compare`) as **Values**.
3. Set the chart type to **Pivot Table**.
4. In the Values section, switch to **Show as rows** so each metric becomes its own row.

## Customization ideas

This pattern is flexible. Here are some ways you can adapt it:

- **Different time periods.** Add or replace `case()` branches for windows like `QTD`, `QTD LY`, `Last Week`, or `Last Month`. Just add corresponding rows in the `time_type` query model and matching `when:` branches in each metric.

- **More metrics.** Apply the same `case()` pattern to any base measure (revenue per active customer, refund rate, etc). Each new metric follows the identical structure.

- **Custom sort order.** The number prefixes (`1.`, `2.`, etc.) control column ordering in the pivot. Adjust or remove them depending on how you want the columns arranged.