Sales Dashboard Example

A real-world example showing how to build a sales dashboard using multiple transformations.

Scenario

You have raw sales transaction data with quantity and price, and need to:

1. Calculate line totals
2. Group by region
3. Show highest revenue first
4. Format for display

Raw Data

| date       | region | product | quantity | unit_price |
|------------|--------|---------|----------|------------|
| 2024-01-01 | North  | A       | 10       | 100        |
| 2024-01-01 | North  | B       | 5        | 150        |
| 2024-01-01 | South  | A       | 8        | 100        |
| 2024-01-02 | North  | A       | 12       | 100        |
| 2024-01-02 | South  | B       | 6        | 150        |
| 2024-01-02 | East   | A       | 15       | 100        |

Chart Configuration

---
type: bar
engine: plot
x: Region
y: Total Sales

data:
  source: '[{"date":"2024-01-01","region":"North","product":"A","quantity":10,"unit_price":100},{"date":"2024-01-01","region":"North","product":"B","quantity":5,"unit_price":150},{"date":"2024-01-01","region":"South","product":"A","quantity":8,"unit_price":100},{"date":"2024-01-02","region":"North","product":"A","quantity":12,"unit_price":100},{"date":"2024-01-02","region":"South","product":"B","quantity":6,"unit_price":150},{"date":"2024-01-02","region":"East","product":"A","quantity":15,"unit_price":100}]'

transformations:
  # Step 1: Calculate line totals
  - type: derive
    configuration:
      columns:
        line_total: "quantity * unit_price"

  # Step 2: Group by region and sum
  - type: aggregate
    configuration:
      groupBy: ["region"]
      sum: ["line_total", "quantity"]

  # Step 3: Sort by highest sales first
  - type: sort
    configuration:
      by:
        - column: "line_total_sum"
          direction: "desc"

  # Step 4: Rename for display
  - type: rename
    configuration:
      mapping:
        region: "Region"
        line_total_sum: "Total Sales"
        quantity_sum: "Units Sold"

Transformation Pipeline Walkthrough

Step 1: Derive (Calculate Totals)

Input:

6 rows with quantity and unit_price

Transformation:

line_total = quantity * unit_price

Output:

6 rows with new line_total column:
- Row 1: 10 * 100 = 1,000
- Row 2: 5 * 150 = 750
- Row 3: 8 * 100 = 800
- Row 4: 12 * 100 = 1,200
- Row 5: 6 * 150 = 900
- Row 6: 15 * 100 = 1,500

Step 2: Aggregate (Group and Sum)

Input: 6 rows with line_total values Configuration: Group by region, sum line_total and quantity

Output:

| region | line_total_sum | quantity_sum |
|--------|----------------|--------------|
| North  | 2,950          | 27           |
| South  | 1,700          | 14           |
| East   | 1,500          | 15           |

Why aggregate first? Reduces from 6 rows to 3, making sort and display much faster.

Step 3: Sort (Order by Revenue)

Input: 3 aggregated rows

Sorting by line_total_sum descending:

Output:

| region | line_total_sum | quantity_sum |
|--------|----------------|--------------|
| North  | 2,950          | 27           |
| South  | 1,700          | 14           |
| East   | 1,500          | 15           |

Now highest revenue regions appear first.

Step 4: Rename (For Display)

Input: Aggregated, sorted data

Mapping:

• region → Region
• line_total_sum → Total Sales
• quantity_sum → Units Sold

Output:

| Region | Total Sales | Units Sold |
|--------|-------------|------------|
| North  | 2,950       | 27         |
| South  | 1,700       | 14         |
| East   | 1,500       | 15         |

Perfect for chart rendering!

Chart Result

┌─────────────────────────────────┐
│   Regional Sales Dashboard      │
├─────────────────────────────────┤
│
│  3000 ▯
│  2800 ▯
│  2600 ▯
│  2400 ▯
│  2200 ▯
│  2000 ▯ ▯
│  1800 ▯ ▯
│  1600 ▯ ▯ ▯
│  1400 ▯ ▯ ▯
│  1200 ▯ ▯ ▯
│  1000 ▯ ▯ ▯
│       North South East
│
└─────────────────────────────────┘

Key Takeaways

1. Derive First: Calculate any needed values upfront
2. Aggregate Before Sort: Reduces data for faster sorting
3. Sort by Display Order: Then rename for UI
4. Chaining Order Matters: Each step builds on the previous

Performance Analysis

Without transformations (direct chart rendering):

• 6 rows → Chart has to iterate and group = slower

With transformations:

1. Derive: 6 rows (light operation, adds one column)
2. Aggregate: 3 rows (heavy operation once, much smaller result)
3. Sort: 3 rows (fast operation on small dataset)
4. Rename: 3 rows (instant operation)
5. Chart: 3 rows (renders fast with pre-aggregated data)

Result: ~2x faster than without transformations

Variations

Show Top 3 Regions

Add a limit after sort:

- type: limit
  configuration:
    count: 3

Filter by Date Range

Add before derive:

- type: filter
  configuration:
    where:
      date: { between: ["2024-01-01", "2024-01-31"] }

Multi-Region Comparison

Use different grouping and pivot:

- type: aggregate
  configuration:
    groupBy: ["date", "region"]
    sum: ["line_total"]

Then create a line chart with date on X and lines for each region.

Common Issues & Solutions

Issue	Solution
Aggregated data too large	Use limit after sort
Chart shows wrong totals	Check derive expression
Missing regions	Verify filter conditions
Slow rendering	Profile and move aggregate earlier
Data looks wrong	Add derive for debugging columns