Malloy Documentation
search
previousTables
Numbersnext

Big Value Cards

VSCode document

The # big_value tag renders aggregate values as prominent metric cards, ideal for KPIs and summary statistics. Big values support number formatting, comparison indicators, sparklines, and documentation tooltips.

Properties

Property Description Example
.size Card size: sm, md, lg # big_value { size=lg }
.sparkline Name of a hidden nested view to use as sparkline # big_value { sparkline=trend }
.comparison_field Field to compare against (shows delta) # big_value { comparison_field=sales }
.comparison_label Label for the comparison # big_value { comparison_label='vs Prior' }
.down_is_good Invert color logic (green for decrease) # big_value { down_is_good=true }
# description Tooltip documentation on the card # description="Help text here"
# label Override the display name # label="Total Revenue"
# hidden Hide the comparison field from display Required on comparison fields

The examples below all use the following semantic model.

document
source: orders is duckdb.table('../data/order_items.parquet') extend {
  dimension: order_month is created_at.month
  measure:
    order_count is count()
    # currency
    total_sales is sale_price.sum()
    # currency
    avg_order is sale_price.avg()
}

Basic Big Value

Tag a query with # big_value and include only aggregate fields. Each measure becomes a card:

document
# big_value
run: orders -> {
  aggregate:
    order_count
    total_sales
    avg_order
}
QUERY RESULTS 
[
  {
    "order_count": 271019,
    "total_sales": 12566292.875474572,
    "avg_order": 46.36683360013347
  }
]
SELECT 
   COUNT(1) as "order_count",
   COALESCE(SUM(base."sale_price"),0) as "total_sales",
   AVG(base."sale_price") as "avg_order"
FROM '../data/order_items.parquet' as base

Formatted Metrics

Big value cards respect number formatting tags like # currency, # percent, and # number:

document
# big_value
run: orders -> {
  aggregate:
    # label="Orders"
    order_count

    # label="Revenue"
    total_sales

    # label="Avg Order"
    avg_order

    # label="Fulfillment Rate"
    # percent
    fulfillment is count() { where: status = 'Complete' } / count()
}
QUERY RESULTS 
[
  {
    "order_count": 271019,
    "total_sales": 12566292.875474572,
    "avg_order": 46.36683360013347,
    "fulfillment": 0.94415151705231
  }
]
SELECT 
   COUNT(1) as "order_count",
   COALESCE(SUM(base."sale_price"),0) as "total_sales",
   AVG(base."sale_price") as "avg_order",
   (COUNT(CASE WHEN base."status"='Complete' THEN 1 END))*1.0/COUNT(1) as "fulfillment"
FROM '../data/order_items.parquet' as base

Comparison Indicators

To show a delta (percentage change) next to a metric, add a hidden comparison field that references it. The comparison field's value is the "prior" value — the renderer computes the percentage change automatically.

When the current value is higher than the comparison value, the delta shows green (up is good). When lower, it shows red.

document
# big_value
run: orders -> {
  aggregate:
    # label="Current Sales"
    # description="Total revenue from all orders"
    total_sales

    // Simulating a "prior period" value for comparison
    # big_value { comparison_field=total_sales comparison_label='vs Prior Period' }
    # hidden
    prior_sales is sale_price.sum() * 0.85
}
QUERY RESULTS 
[
  {
    "total_sales": 12566292.875474572,
    "prior_sales": 10681348.944153385
  }
]
SELECT 
   COALESCE(SUM(base."sale_price"),0) as "total_sales",
   COALESCE(SUM(base."sale_price"),0)*0.85::DOUBLE as "prior_sales"
FROM '../data/order_items.parquet' as base

Down is Good

For cost metrics where a decrease is positive, set down_is_good=true. This inverts the color logic so decreases show green:

document
# big_value
run: orders -> {
  aggregate:
    # label="Avg Shipping Cost"
    # currency
    # description="Average cost per order. Lower is better."
    current_cost is avg(sale_price * 0.15)

    # big_value { comparison_field=current_cost comparison_label='vs Budget' down_is_good=true }
    # hidden
    budget_cost is avg(sale_price * 0.15) * 1.10
}
QUERY RESULTS 
[
  {
    "current_cost": 6.955025040021019,
    "budget_cost": 7.650527544023122
  }
]
SELECT 
   AVG(base."sale_price"*0.15::DOUBLE) as "current_cost",
   AVG(base."sale_price"*0.15::DOUBLE)*1.10::DOUBLE as "budget_cost"
FROM '../data/order_items.parquet' as base

Sparklines

Sparklines add a small trend chart to a big value card. Define a hidden nested view with a chart tag, then reference it by name using the sparkline property.

Line Sparkline

document
# big_value
run: orders -> {
  aggregate:
    # label="Total Revenue"
    # big_value { sparkline=trend }
    total_sales

  # line_chart { size=spark }
  # hidden
  nest: trend is {
    group_by: order_month
    aggregate: total_sales
    order_by: order_month
    limit: 24
  }
}
QUERY RESULTS 
[
  {
    "total_sales": 12566292.875474572,
    "trend": [
      {
        "order_month": "2019-01-01T00:00:00.000Z",
        "total_sales": 56429.910190582275
      },
      {
        "order_month": "2019-02-01T00:00:00.000Z",
        "total_sales": 56442.200234651566
      },
      {
        "order_month": "2019-03-01T00:00:00.000Z",
        "total_sales": 74186.66035795212
      },
      {
        "order_month": "2019-04-01T00:00:00.000Z",
        "total_sales": 83191.01023316383
      },
      {
        "order_month": "2019-05-01T00:00:00.000Z",
        "total_sales": 94457.05022394657
      },
      {
        "order_month": "2019-06-01T00:00:00.000Z",
        "total_sales": 99809.20037472248
      },
      {
        "order_month": "2019-07-01T00:00:00.000Z",
        "total_sales": 111521.71043860912
      },
      {
        "order_month": "2019-08-01T00:00:00.000Z",
        "total_sales": 119171.38046646118
      },
      {
        "order_month": "2019-09-01T00:00:00.000Z",
        "total_sales": 133723.26046347618
      },
      {
        "order_month": "2019-10-01T00:00:00.000Z",
        "total_sales": 152780.040563941
      },
      {
        "order_month": "2019-11-01T00:00:00.000Z",
        "total_sales": 153293.34058189392
      },
      {
        "order_month": "2019-12-01T00:00:00.000Z",
        "total_sales": 183589.78070259094
      },
      {
        "order_month": "2020-01-01T00:00:00.000Z",
        "total_sales": 174263.71071588993
      },
      {
        "order_month": "2020-02-01T00:00:00.000Z",
        "total_sales": 161627.5206091404
      },
      {
        "order_month": "2020-03-01T00:00:00.000Z",
        "total_sales": 195716.51070976257
      },
      {
        "order_month": "2020-04-01T00:00:00.000Z",
        "total_sales": 193023.40073931217
      },
      {
        "order_month": "2020-05-01T00:00:00.000Z",
        "total_sales": 189705.5606878996
      },
      {
        "order_month": "2020-06-01T00:00:00.000Z",
        "total_sales": 195607.51068353653
      },
      {
        "order_month": "2020-07-01T00:00:00.000Z",
        "total_sales": 213664.6306566
      },
      {
        "order_month": "2020-08-01T00:00:00.000Z",
        "total_sales": 228109.46081531048
      },
      {
        "order_month": "2020-09-01T00:00:00.000Z",
        "total_sales": 229698.06079995632
      },
      {
        "order_month": "2020-10-01T00:00:00.000Z",
        "total_sales": 255452.28080511093
      },
      {
        "order_month": "2020-11-01T00:00:00.000Z",
        "total_sales": 267076.81107723713
      },
      {
        "order_month": "2020-12-01T00:00:00.000Z",
        "total_sales": 298097.6110602617
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    CASE WHEN group_set=0 THEN
      COALESCE(SUM(base."sale_price"),0)
      END as "total_sales__0",
    CASE WHEN group_set=1 THEN
      DATE_TRUNC('month', base."created_at")
      END as "order_month__1",
    CASE WHEN group_set=1 THEN
      COALESCE(SUM(base."sale_price"),0)
      END as "total_sales__1"
  FROM '../data/order_items.parquet' as base
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set  ) as group_set
  GROUP BY 1,3
)
, __stage1 AS (
  SELECT
    *,
   CASE WHEN GROUP_SET=1 THEN
                 ROW_NUMBER() OVER (PARTITION BY group_set ORDER BY  "order_month__1" ASC) END  as __row_number__1 
  FROM __stage0
)
, __stage2 AS (
  SELECT * FROM __stage1
   WHERE  NOT ((GROUP_SET = 1 AND __row_number__1 > 24))
)
SELECT
  MAX(CASE WHEN group_set=0 THEN "total_sales__0" END) as "total_sales",
  COALESCE(LIST({
    "order_month": "order_month__1", 
    "total_sales": "total_sales__1"}  ORDER BY  "order_month__1" ASC NULLS LAST) FILTER (WHERE group_set=1),[]) as "trend"
FROM __stage2

Bar Sparkline

document
# big_value
run: orders -> {
  aggregate:
    # label="Order Volume"
    # big_value { sparkline=volume_trend }
    order_count

  # bar_chart { size=spark }
  # hidden
  nest: volume_trend is {
    group_by: order_month
    aggregate: order_count
    order_by: order_month
    limit: 12
  }
}
QUERY RESULTS 
[
  {
    "order_count": 271019,
    "volume_trend": [
      {
        "order_month": "2019-01-01T00:00:00.000Z",
        "order_count": 1262
      },
      {
        "order_month": "2019-02-01T00:00:00.000Z",
        "order_count": 1230
      },
      {
        "order_month": "2019-03-01T00:00:00.000Z",
        "order_count": 1507
      },
      {
        "order_month": "2019-04-01T00:00:00.000Z",
        "order_count": 1826
      },
      {
        "order_month": "2019-05-01T00:00:00.000Z",
        "order_count": 2044
      },
      {
        "order_month": "2019-06-01T00:00:00.000Z",
        "order_count": 2150
      },
      {
        "order_month": "2019-07-01T00:00:00.000Z",
        "order_count": 2382
      },
      {
        "order_month": "2019-08-01T00:00:00.000Z",
        "order_count": 2632
      },
      {
        "order_month": "2019-09-01T00:00:00.000Z",
        "order_count": 2902
      },
      {
        "order_month": "2019-10-01T00:00:00.000Z",
        "order_count": 3214
      },
      {
        "order_month": "2019-11-01T00:00:00.000Z",
        "order_count": 3387
      },
      {
        "order_month": "2019-12-01T00:00:00.000Z",
        "order_count": 4038
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    CASE WHEN group_set=0 THEN
      COUNT(1)
      END as "order_count__0",
    CASE WHEN group_set=1 THEN
      DATE_TRUNC('month', base."created_at")
      END as "order_month__1",
    CASE WHEN group_set=1 THEN
      COUNT(1)
      END as "order_count__1"
  FROM '../data/order_items.parquet' as base
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set  ) as group_set
  GROUP BY 1,3
)
, __stage1 AS (
  SELECT
    *,
   CASE WHEN GROUP_SET=1 THEN
                 ROW_NUMBER() OVER (PARTITION BY group_set ORDER BY  "order_month__1" ASC) END  as __row_number__1 
  FROM __stage0
)
, __stage2 AS (
  SELECT * FROM __stage1
   WHERE  NOT ((GROUP_SET = 1 AND __row_number__1 > 12))
)
SELECT
  MAX(CASE WHEN group_set=0 THEN "order_count__0" END) as "order_count",
  COALESCE(LIST({
    "order_month": "order_month__1", 
    "order_count": "order_count__1"}  ORDER BY  "order_month__1" ASC NULLS LAST) FILTER (WHERE group_set=1),[]) as "volume_trend"
FROM __stage2

Full Example

Combining comparison, sparkline, and documentation in a single big value:

document
# big_value
run: orders -> {
  aggregate:
    # description="Total revenue from all orders. Updated daily."
    # label="Total Revenue"
    # big_value { sparkline=trend }
    total_sales

    # big_value { comparison_field=total_sales comparison_label='vs Last Year' }
    # hidden
    prior_revenue is sale_price.sum() * 0.85

  # line_chart { size=spark }
  # hidden
  nest: trend is {
    group_by: order_month
    aggregate: total_sales
    order_by: order_month
    limit: 24
  }
}
QUERY RESULTS 
[
  {
    "total_sales": 12566292.875474572,
    "prior_revenue": 10681348.944153385,
    "trend": [
      {
        "order_month": "2019-01-01T00:00:00.000Z",
        "total_sales": 56429.910190582275
      },
      {
        "order_month": "2019-02-01T00:00:00.000Z",
        "total_sales": 56442.200234651566
      },
      {
        "order_month": "2019-03-01T00:00:00.000Z",
        "total_sales": 74186.66035795212
      },
      {
        "order_month": "2019-04-01T00:00:00.000Z",
        "total_sales": 83191.01023316383
      },
      {
        "order_month": "2019-05-01T00:00:00.000Z",
        "total_sales": 94457.05022394657
      },
      {
        "order_month": "2019-06-01T00:00:00.000Z",
        "total_sales": 99809.20037472248
      },
      {
        "order_month": "2019-07-01T00:00:00.000Z",
        "total_sales": 111521.71043860912
      },
      {
        "order_month": "2019-08-01T00:00:00.000Z",
        "total_sales": 119171.38046646118
      },
      {
        "order_month": "2019-09-01T00:00:00.000Z",
        "total_sales": 133723.26046347618
      },
      {
        "order_month": "2019-10-01T00:00:00.000Z",
        "total_sales": 152780.040563941
      },
      {
        "order_month": "2019-11-01T00:00:00.000Z",
        "total_sales": 153293.34058189392
      },
      {
        "order_month": "2019-12-01T00:00:00.000Z",
        "total_sales": 183589.78070259094
      },
      {
        "order_month": "2020-01-01T00:00:00.000Z",
        "total_sales": 174263.71071588993
      },
      {
        "order_month": "2020-02-01T00:00:00.000Z",
        "total_sales": 161627.5206091404
      },
      {
        "order_month": "2020-03-01T00:00:00.000Z",
        "total_sales": 195716.51070976257
      },
      {
        "order_month": "2020-04-01T00:00:00.000Z",
        "total_sales": 193023.40073931217
      },
      {
        "order_month": "2020-05-01T00:00:00.000Z",
        "total_sales": 189705.5606878996
      },
      {
        "order_month": "2020-06-01T00:00:00.000Z",
        "total_sales": 195607.51068353653
      },
      {
        "order_month": "2020-07-01T00:00:00.000Z",
        "total_sales": 213664.6306566
      },
      {
        "order_month": "2020-08-01T00:00:00.000Z",
        "total_sales": 228109.46081531048
      },
      {
        "order_month": "2020-09-01T00:00:00.000Z",
        "total_sales": 229698.06079995632
      },
      {
        "order_month": "2020-10-01T00:00:00.000Z",
        "total_sales": 255452.28080511093
      },
      {
        "order_month": "2020-11-01T00:00:00.000Z",
        "total_sales": 267076.81107723713
      },
      {
        "order_month": "2020-12-01T00:00:00.000Z",
        "total_sales": 298097.6110602617
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    CASE WHEN group_set=0 THEN
      COALESCE(SUM(base."sale_price"),0)
      END as "total_sales__0",
    CASE WHEN group_set=0 THEN
      COALESCE(SUM(base."sale_price"),0)
      END*0.85::DOUBLE as "prior_revenue__0",
    CASE WHEN group_set=1 THEN
      DATE_TRUNC('month', base."created_at")
      END as "order_month__1",
    CASE WHEN group_set=1 THEN
      COALESCE(SUM(base."sale_price"),0)
      END as "total_sales__1"
  FROM '../data/order_items.parquet' as base
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set  ) as group_set
  GROUP BY 1,4
)
, __stage1 AS (
  SELECT
    *,
   CASE WHEN GROUP_SET=1 THEN
                 ROW_NUMBER() OVER (PARTITION BY group_set ORDER BY  "order_month__1" ASC) END  as __row_number__1 
  FROM __stage0
)
, __stage2 AS (
  SELECT * FROM __stage1
   WHERE  NOT ((GROUP_SET = 1 AND __row_number__1 > 24))
)
SELECT
  MAX(CASE WHEN group_set=0 THEN "total_sales__0" END) as "total_sales",
  MAX(CASE WHEN group_set=0 THEN "prior_revenue__0" END) as "prior_revenue",
  COALESCE(LIST({
    "order_month": "order_month__1", 
    "total_sales": "total_sales__1"}  ORDER BY  "order_month__1" ASC NULLS LAST) FILTER (WHERE group_set=1),[]) as "trend"
FROM __stage2

Size Variants

document
# big_value { size=lg }
run: orders -> {
  aggregate:
    # label="Large Cards"
    total_sales
    avg_order
}
QUERY RESULTS 
[
  {
    "total_sales": 12566292.875474572,
    "avg_order": 46.36683360013347
  }
]
SELECT 
   COALESCE(SUM(base."sale_price"),0) as "total_sales",
   AVG(base."sale_price") as "avg_order"
FROM '../data/order_items.parquet' as base
document
# big_value { size=sm }
run: orders -> {
  aggregate:
    # label="Small Cards"
    total_sales
    avg_order
}
QUERY RESULTS 
[
  {
    "total_sales": 12566292.875474572,
    "avg_order": 46.36683360013347
  }
]
SELECT 
   COALESCE(SUM(base."sale_price"),0) as "total_sales",
   AVG(base."sale_price") as "avg_order"
FROM '../data/order_items.parquet' as base

Big Values in Dashboards

Big values work inside dashboards as nested views. This lets you combine KPI cards with tables and charts:

document
# dashboard
run: orders -> {
  group_by: status
  nest:
    # big_value
    kpis is {
      aggregate:
        # label="Orders"
        order_count
        # label="Revenue"
        total_sales
    }
    # break
    by_month is {
      group_by: order_month
      aggregate: total_sales
      order_by: order_month desc
      limit: 6
    }
}
QUERY RESULTS 
[
  {
    "status": "Cancelled",
    "kpis": {
      "order_count": 9510,
      "total_sales": 446383.4416334629
    },
    "by_month": [
      {
        "order_month": "2022-11-01T00:00:00.000Z",
        "total_sales": 6907.700030326843
      },
      {
        "order_month": "2022-10-01T00:00:00.000Z",
        "total_sales": 21436.03005003929
      },
      {
        "order_month": "2022-09-01T00:00:00.000Z",
        "total_sales": 20262.36007642746
      },
      {
        "order_month": "2022-08-01T00:00:00.000Z",
        "total_sales": 18369.98005747795
      },
      {
        "order_month": "2022-07-01T00:00:00.000Z",
        "total_sales": 16842.040077209473
      },
      {
        "order_month": "2022-06-01T00:00:00.000Z",
        "total_sales": 14860.93007183075
      }
    ]
  },
  {
    "status": "Complete",
    "kpis": {
      "order_count": 255883,
      "total_sales": 11865343.562779665
    },
    "by_month": [
      {
        "order_month": "2022-11-01T00:00:00.000Z",
        "total_sales": 92794.44028234482
      },
      {
        "order_month": "2022-10-01T00:00:00.000Z",
        "total_sales": 505364.4119977951
      },
      {
        "order_month": "2022-09-01T00:00:00.000Z",
        "total_sales": 496349.38183534145
      },
      {
        "order_month": "2022-08-01T00:00:00.000Z",
        "total_sales": 461622.84157812595
      },
      {
        "order_month": "2022-07-01T00:00:00.000Z",
        "total_sales": 425754.6618356705
      },
      {
        "order_month": "2022-06-01T00:00:00.000Z",
        "total_sales": 410060.42133283615
      }
    ]
  },
  {
    "status": "Processing",
    "kpis": {
      "order_count": 1227,
      "total_sales": 52798.08022522926
    },
    "by_month": [
      {
        "order_month": "2022-11-01T00:00:00.000Z",
        "total_sales": 52798.08022522926
      }
    ]
  },
  {
    "status": "Returned",
    "kpis": {
      "order_count": 2672,
      "total_sales": 122077.2305123806
    },
    "by_month": [
      {
        "order_month": "2022-11-01T00:00:00.000Z",
        "total_sales": 2081.410015106201
      },
      {
        "order_month": "2022-10-01T00:00:00.000Z",
        "total_sales": 4923.200011730194
      },
      {
        "order_month": "2022-09-01T00:00:00.000Z",
        "total_sales": 5085.690017223358
      },
      {
        "order_month": "2022-08-01T00:00:00.000Z",
        "total_sales": 5054.750021934509
      },
      {
        "order_month": "2022-07-01T00:00:00.000Z",
        "total_sales": 4983.790022850037
      },
      {
        "order_month": "2022-06-01T00:00:00.000Z",
        "total_sales": 3811.450016975403
      }
    ]
  },
  {
    "status": "Shipped",
    "kpis": {
      "order_count": 1727,
      "total_sales": 79690.56032383442
    },
    "by_month": [
      {
        "order_month": "2022-11-01T00:00:00.000Z",
        "total_sales": 79198.32032120228
      },
      {
        "order_month": "2022-10-01T00:00:00.000Z",
        "total_sales": 492.2400026321411
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    base."status" as "status__0",
    CASE WHEN group_set=1 THEN
      COUNT(1)
      END as "order_count__1",
    CASE WHEN group_set=1 THEN
      COALESCE(SUM(base."sale_price"),0)
      END as "total_sales__1",
    CASE WHEN group_set=2 THEN
      DATE_TRUNC('month', base."created_at")
      END as "order_month__2",
    CASE WHEN group_set=2 THEN
      COALESCE(SUM(base."sale_price"),0)
      END as "total_sales__2"
  FROM '../data/order_items.parquet' as base
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,2,1)) as group_set  ) as group_set
  GROUP BY 1,2,5
)
, __stage1 AS (
  SELECT
    *,
   CASE WHEN GROUP_SET=2 THEN
                 ROW_NUMBER() OVER (PARTITION BY CAST("status__0" as VARCHAR), group_set ORDER BY  "order_month__2" desc) END  as __row_number__2 
  FROM __stage0
)
, __stage2 AS (
  SELECT * FROM __stage1
   WHERE  NOT ((GROUP_SET = 2 AND __row_number__2 > 6))
)
SELECT
  "status__0" as "status",
  COALESCE(FIRST({"order_count": "order_count__1" , "total_sales": "total_sales__1" }) FILTER(WHERE group_set=1), {"order_count": NULL, "total_sales": NULL}) as "kpis",
  COALESCE(LIST({
    "order_month": "order_month__2", 
    "total_sales": "total_sales__2"}  ORDER BY  "order_month__2" DESC NULLS LAST) FILTER (WHERE group_set=2),[]) as "by_month"
FROM __stage2
GROUP BY 1
ORDER BY 1 asc NULLS LAST