Malloy Documentation
search
previousOverview
Pivoted Tablesnext

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.

The following examples that follow use the Malloy semantic data model below.

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
carrierpercent_​of_​flights
WN25.74%
US10.93%
AA10.03%
NW9.74%
UA9.5%
DL9.32%
RU4.66%
MQ4.6%
EV4.57%
HP2.83%
AS2.45%
CO2.07%
B61.4%
OH1.28%
TZ0.88%
[
  {
    "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
      flights."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 flights
  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's (Excel) string definitions 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%)
# format="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
integerdollarsneg_​dollars
344827$344,827($344,827)
[
  {
    "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 flights

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".

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_​datelongest_​flight_​timetotal_​flight_​timeflight_​count
2005-12-316 hours 2 minutes12 days 9 hours175
2005-12-307 hours 44 minutes14 days 14 hours188
2005-12-298 hours 5 minutes13 days 20 hours186
2005-12-287 hours 20 minutes14 days 3 hours197
2005-12-278 hours14 days 3 hours197
2005-12-268 hours13 days 16 hours194
2005-12-251 day 8 hours13 days 5 hours167
2005-12-248 hours 9 minutes11 days 21 hours159
2005-12-237 hours 47 minutes14 days 1 hour201
2005-12-227 hours 46 minutes13 days 10 hours200
2005-12-215 hours 21 minutes14 days 21 minutes192
2005-12-207 hours 29 minutes14 days 11 hours203
2005-12-195 hours 46 minutes12 days 20 hours207
2005-12-187 hours 44 minutes14 days 7 hours179
2005-12-175 hours 19 minutes11 days 2 hours139
2005-12-167 hours 35 minutes15 days 6 hours195
2005-12-156 hours 52 minutes14 days 8 hours204
2005-12-147 hours 35 minutes15 days 39 minutes193
2005-12-135 hours 26 minutes13 days 18 hours190
2005-12-127 hours 29 minutes13 days 18 hours176
[
  {
    "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', flights."dep_time") as "dep_date",
   max(flights."flight_time") as "longest_flight_time",
   COALESCE(SUM(flights."flight_time"),0) as "total_flight_time",
   COUNT( 1) as "flight_count"
FROM '../data/flights.parquet' as flights
GROUP BY 1
ORDER BY 1 desc NULLS LAST
LIMIT 20