Malloy Documentation
search
previousBig Values
Links / Imagesnext

Rendering Numbers

VSCode document

Malloy's render provides a variety of ways to render numeric values. Tagging a number will change how the number is displayed in the result set but the underlying value doesn't change.

Number Format Tags

Tag Description Examples
# number Custom number format # number="$#,##0", # number=1m, # number=auto, # number=id
# percent Format as percentage # percent
# currency Currency format # currency, # currency=usd2m, # currency=eur
# duration Duration format # duration=seconds, # duration=minutes, # duration.terse
# data_volume Storage size format # data_volume=bytes, # data_volume=mb

The examples on this page use the following semantic model.

document
source: airports is duckdb.table('../data/airports.parquet') extend {
  dimension: name is concat(code, ' - ', full_name)
  measure: airport_count is count()
}

source: flights is duckdb.table('../data/flights.parquet') extend {
  join_one: orig is airports on origin = orig.code
  join_one: dest is airports on destination = dest.code

  measure: flight_count is count()
}

# percent

Carriers as percentage of flights

document
run: flights -> {
  group_by: carrier
  # percent
  aggregate: percent_of_flights is flight_count / all(flight_count)
}
QUERY RESULTS 
[
  {
    "carrier": "WN",
    "percent_of_flights": 0.2573783375431738
  },
  {
    "carrier": "US",
    "percent_of_flights": 0.10928088577750582
  },
  {
    "carrier": "AA",
    "percent_of_flights": 0.10027347046489979
  },
  {
    "carrier": "NW",
    "percent_of_flights": 0.09738216554968143
  },
  {
    "carrier": "UA",
    "percent_of_flights": 0.09499546149228454
  },
  {
    "carrier": "DL",
    "percent_of_flights": 0.09317715840116929
  },
  {
    "carrier": "RU",
    "percent_of_flights": 0.046614679244954715
  },
  {
    "carrier": "MQ",
    "percent_of_flights": 0.046020178234302996
  },
  {
    "carrier": "EV",
    "percent_of_flights": 0.04573017774130216
  },
  {
    "carrier": "HP",
    "percent_of_flights": 0.028275048067581715
  },
  {
    "carrier": "AS",
    "percent_of_flights": 0.024513741673360845
  },
  {
    "carrier": "CO",
    "percent_of_flights": 0.020703135195329833
  },
  {
    "carrier": "B6",
    "percent_of_flights": 0.01404182387110058
  },
  {
    "carrier": "OH",
    "percent_of_flights": 0.012818021790637044
  },
  {
    "carrier": "TZ",
    "percent_of_flights": 0.00879571495271542
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    CASE WHEN group_set=1 THEN
      base."carrier"
      END as "carrier__1",
    (CASE WHEN group_set=1 THEN
      COUNT(1)
      END)*1.0/MAX((CASE WHEN group_set=0 THEN
      COUNT(1)
      END)) OVER () as "percent_of_flights__1"
  FROM '../data/flights.parquet' as base
  CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set  ) as group_set
  GROUP BY 1,2
)
SELECT
  "carrier__1" as "carrier",
  MAX(CASE WHEN group_set=1 THEN "percent_of_flights__1" END) as "percent_of_flights"
FROM __stage0
WHERE group_set NOT IN (0)
GROUP BY 1
ORDER BY 2 desc NULLS LAST

# number

Malloy uses LookML-style format strings (similar to Excel custom number formats) for formatting numbers.

# number="0"             # Integer (123)
# number="*00#"          # Integer zero-padded to 3 places (001)
# number='0 "String"'    # Integer followed by a string (123 String)
                              #   Note "String" can be replaced with any other word

# number="0.##"          # Number up to 2 decimals (1. or 1.2 or 1.23)
# number="0.00"          # Number with exactly 2 decimals (1.23)
# number="*00#.00"       # Number zero-padded to 3 places and exactly 2 decimals (001.23)
# number="#,##0"         # Number with comma between thousands (1,234)
# number="#,##0.00"      # Number with comma between thousands and 2 decimals (1,234.00)
# number='0.000,,"M"'    # Number in millions with 3 decimals (1.234 M)
                              #   Note division by 1 million happens automatically
# number='0.000,"K"'     # Number in thousands with 3 decimals (1.234 K)
                              #   Note division by 1 thousand happens automatically

# number="$0"            # Dollars with 0 decimals ($123)
# number="$0.00"         # Dollars with 2 decimals ($123.00)
# number='"€"0'          # Euros with 0 decimals (€123)
# number="$#,##0.00"     # Dollars with comma btwn thousands and 2 decimals ($1,234.00)
# number="$#.00;($#.00)" # Dollars with 2 decimals, positive values displayed
                              #   normally, negative values wrapped in parenthesis

# number='0\%'           # Display as percent with 0 decimals (1 becomes 1%)
# number='0.00\%'        # Display as percent with 2 decimals (1 becomes 1.00%)
# number="0%"            # Convert to percent with 0 decimals (.01 becomes 1%)
# number="0.00%"         # Convert to percent with 2 decimals (.01 becomes 1.00%)
document
run: flights -> {
  // tag a single element
  aggregate:
    # number="0"
    `integer` is flight_count

  // tag multiple elements at once.
  # number="$#,##0;($#,##0)"
  aggregate:
    dollars is flight_count
    neg_dollars is 0 - flight_count
}
QUERY RESULTS 
[
  {
    "integer": 344827,
    "dollars": 344827,
    "neg_dollars": -344827
  }
]
SELECT 
   (COUNT(1)) as "integer",
   (COUNT(1)) as "dollars",
   0-(COUNT(1)) as "neg_dollars"
FROM '../data/flights.parquet' as base

# number=big

The # number=big renderer formats large numbers with abbreviated notation and a single decimal place for easier readability. Numbers are automatically scaled and labeled with the appropriate magnitude:

  • K for thousands (1,000)

  • M for millions (1,000,000)

  • B for billions (1,000,000,000)

  • T for trillions (1,000,000,000,000)

  • Q for quadrillions (1,000,000,000,000,000)

# number shorthand

Pattern: # number={decimals}{scale} where scale is k, m, b, t, q, or auto.

Shorthand Description Example output
# number=1k 1 decimal, scale to thousands 42.5k
# number=1m 1 decimal, scale to millions 42.5m
# number=auto Auto-select scale based on magnitude 42.5m or 1.2k
# number=id Raw number with no comma separators 123456
# number=big Abbreviated with 1 decimal (legacy) 42.5M
document
# transpose
run: flights -> {
  aggregate:
    # number=1k
    thousands is flight_count
    # number=1m
    millions is flight_count * 1000
    # number=auto
    auto_scaled is flight_count * 1000
    # number=id
    as_id is flight_count
}
QUERY RESULTS 
[
  {
    "thousands": 344827,
    "millions": 344827000,
    "auto_scaled": 344827000,
    "as_id": 344827
  }
]
SELECT 
   (COUNT(1)) as "thousands",
   (COUNT(1))*1000 as "millions",
   (COUNT(1))*1000 as "auto_scaled",
   (COUNT(1)) as "as_id"
FROM '../data/flights.parquet' as base

# number verbose syntax

For finer control, use the verbose object syntax:

# number { scale=m decimals=1 suffix=word }
Property Values Description
scale k, m, b, t, q, auto Divide by this magnitude
decimals 0, 1, 2, ... Number of decimal places
suffix word, scientific, letter Suffix style after the number

Suffix styles:

  • word: full word (e.g. "42.5 million")

  • scientific: scientific notation style

  • letter: single uppercase letter (e.g. "42.5M")

document
# transpose
run: flights -> {
  aggregate:
    # number { scale=m decimals=1 suffix=word }
    word_style is flight_count * 1000

    # number { scale=m decimals=2 suffix=scientific }
    scientific_style is flight_count * 1000

    # number { scale=m decimals=1 suffix=letter }
    letter_style is flight_count * 1000
}
QUERY RESULTS 
[
  {
    "word_style": 344827000,
    "scientific_style": 344827000,
    "letter_style": 344827000
  }
]
SELECT 
   (COUNT(1))*1000 as "word_style",
   (COUNT(1))*1000 as "scientific_style",
   (COUNT(1))*1000 as "letter_style"
FROM '../data/flights.parquet' as base

# currency

# currency shorthand

Pattern: # currency={code}{decimals}{scale} — e.g. # currency=usd2m for "$42.5m".

Codes: usd ($), eur (€), gbp (£). Scale and suffix options match # number.

# currency verbose syntax

For finer control, use the verbose object syntax:

# currency { scale=m decimals=2 suffix=finance }
Property Values Description
scale k, m, b, t, q, auto Divide by this magnitude
decimals 0, 1, 2, ... Number of decimal places
suffix finance, word, none, auto Suffix style

Suffix styles:

  • finance: financial notation (e.g. "$42.54MM")

  • word: full word (e.g. "$42.5 million")

  • none: no suffix, just the scaled number

  • auto: automatically choose a suffix

document
# transpose
run: flights -> {
  aggregate:
    # currency=usd1m
    usd_shorthand is flight_count * 1000

    # currency { scale=m decimals=2 suffix=finance }
    finance_style is flight_count * 1000

    # currency { scale=m decimals=1 suffix=word }
    word_style is flight_count * 1000

    # currency { scale=k suffix=none }
    no_suffix is flight_count
}
QUERY RESULTS 
[
  {
    "usd_shorthand": 344827000,
    "finance_style": 344827000,
    "word_style": 344827000,
    "no_suffix": 344827
  }
]
SELECT 
   (COUNT(1))*1000 as "usd_shorthand",
   (COUNT(1))*1000 as "finance_style",
   (COUNT(1))*1000 as "word_style",
   (COUNT(1)) as "no_suffix"
FROM '../data/flights.parquet' as base

Durations

The # duration renderer interprets a value as a number of seconds and renders it as a human-adjusted duration. Other units can be specified like # duration="minutes", with possible units of "nanoseconds", "microseconds", "milliseconds", "seconds", "minutes", "hours", and "days".

# duration properties

Property Description
=unit Input unit: nanoseconds, milliseconds, seconds (default), minutes, hours, days
.terse Abbreviated units (ns, µs, ms, s, m, h, d)
.number SSF format for numeric parts: # duration { number="0.0" }
document
run: flights -> {
  group_by: dep_date is dep_time.day
  # duration="minutes"
  aggregate:
    longest_flight_time is max(flight_time)
    total_flight_time is flight_time.sum()
  aggregate:
    flight_count
  limit: 20
}
QUERY RESULTS 
[
  {
    "dep_date": "2005-12-31T00:00:00.000Z",
    "longest_flight_time": "362",
    "total_flight_time": "17874",
    "flight_count": 175
  },
  {
    "dep_date": "2005-12-30T00:00:00.000Z",
    "longest_flight_time": "464",
    "total_flight_time": "21054",
    "flight_count": 188
  },
  {
    "dep_date": "2005-12-29T00:00:00.000Z",
    "longest_flight_time": "485",
    "total_flight_time": "19923",
    "flight_count": 186
  },
  {
    "dep_date": "2005-12-28T00:00:00.000Z",
    "longest_flight_time": "440",
    "total_flight_time": "20367",
    "flight_count": 197
  },
  {
    "dep_date": "2005-12-27T00:00:00.000Z",
    "longest_flight_time": "480",
    "total_flight_time": "20381",
    "flight_count": 197
  },
  {
    "dep_date": "2005-12-26T00:00:00.000Z",
    "longest_flight_time": "480",
    "total_flight_time": "19683",
    "flight_count": 194
  },
  {
    "dep_date": "2005-12-25T00:00:00.000Z",
    "longest_flight_time": "1920",
    "total_flight_time": "19037",
    "flight_count": 167
  },
  {
    "dep_date": "2005-12-24T00:00:00.000Z",
    "longest_flight_time": "489",
    "total_flight_time": "17130",
    "flight_count": 159
  },
  {
    "dep_date": "2005-12-23T00:00:00.000Z",
    "longest_flight_time": "467",
    "total_flight_time": "20241",
    "flight_count": 201
  },
  {
    "dep_date": "2005-12-22T00:00:00.000Z",
    "longest_flight_time": "466",
    "total_flight_time": "19360",
    "flight_count": 200
  },
  {
    "dep_date": "2005-12-21T00:00:00.000Z",
    "longest_flight_time": "321",
    "total_flight_time": "20181",
    "flight_count": 192
  },
  {
    "dep_date": "2005-12-20T00:00:00.000Z",
    "longest_flight_time": "449",
    "total_flight_time": "20855",
    "flight_count": 203
  },
  {
    "dep_date": "2005-12-19T00:00:00.000Z",
    "longest_flight_time": "346",
    "total_flight_time": "18514",
    "flight_count": 207
  },
  {
    "dep_date": "2005-12-18T00:00:00.000Z",
    "longest_flight_time": "464",
    "total_flight_time": "20605",
    "flight_count": 179
  },
  {
    "dep_date": "2005-12-17T00:00:00.000Z",
    "longest_flight_time": "319",
    "total_flight_time": "15966",
    "flight_count": 139
  },
  {
    "dep_date": "2005-12-16T00:00:00.000Z",
    "longest_flight_time": "455",
    "total_flight_time": "22007",
    "flight_count": 195
  },
  {
    "dep_date": "2005-12-15T00:00:00.000Z",
    "longest_flight_time": "412",
    "total_flight_time": "20679",
    "flight_count": 204
  },
  {
    "dep_date": "2005-12-14T00:00:00.000Z",
    "longest_flight_time": "455",
    "total_flight_time": "21639",
    "flight_count": 193
  },
  {
    "dep_date": "2005-12-13T00:00:00.000Z",
    "longest_flight_time": "326",
    "total_flight_time": "19801",
    "flight_count": 190
  },
  {
    "dep_date": "2005-12-12T00:00:00.000Z",
    "longest_flight_time": "449",
    "total_flight_time": "19841",
    "flight_count": 176
  }
]
SELECT 
   DATE_TRUNC('day', base."dep_time") as "dep_date",
   max(base."flight_time") as "longest_flight_time",
   COALESCE(SUM(base."flight_time"),0) as "total_flight_time",
   COUNT(1) as "flight_count"
FROM '../data/flights.parquet' as base
GROUP BY 1
ORDER BY 1 desc NULLS LAST
LIMIT 20

Data Volume

The # data_volume tag formats numbers as human-readable storage sizes. Specify the unit of the input value:

Tag Input unit Example output
# data_volume = bytes Bytes 3.7 KB
# data_volume = kb Kilobytes 3.7 MB
# data_volume = mb Megabytes 3.6 GB
# data_volume = gb Gigabytes 3.5 TB
# data_volume = tb Terabytes 3.4 PB

Values are automatically scaled to the most readable unit.

document
run: flights -> {
  aggregate:
    # data_volume = bytes
    total_bytes is flight_count * 1024
    # data_volume = mb
    total_mb is flight_count * 256
}
QUERY RESULTS 
[
  {
    "total_bytes": 353102848,
    "total_mb": 88275712
  }
]
SELECT 
   (COUNT(1))*1024 as "total_bytes",
   (COUNT(1))*256 as "total_mb"
FROM '../data/flights.parquet' as base