VISUALIZE

VISUALIZE picks the metrics to chart, the chart type and how many data points to draw. Without it, the chart follows the shape of the result.

VISUALIZE [<metric> [TYPE <chart>], …] [MAX <n>]
VISUALIZE TYPE <chart> [MAX <n>]

Chart types

Each chart draws a set number of dimensions and metrics. A time series counts as a dimension. When the TYPE you pick can't draw the result, the chart shows as a table and the warning W_CHART_CANNOT_DRAW says what the chart needs.

Stacked charts, donut, treemap, sunburst and waterfall add values up, so they need metrics that add up, such as revenue or room nights. A rate such as occupancy, ADR or cancellation rate doesn't add up across groups, so these charts show it as a table.

Tables and lists

The change in list_with_dimension_values comes from COMPARE TO with WITH PERCENT_CHANGE. Room types ranked by revenue, against the period before:

FROM rooms
  SHOW room_revenue
  DURING last_90_days
  GROUP BY room_type
  COMPARE TO previous_period
  WITH PERCENT_CHANGE
  ORDER BY room_revenue DESC
  VISUALIZE TYPE list_with_dimension_values

Single metric

FROM payments
  SHOW net_payments
  DURING month_to_date
  VISUALIZE TYPE single_metric

Line and area

Revenue each day, against the month before:

FROM rooms
  SHOW room_revenue
  DURING last_month
  TIMESERIES day
  COMPARE TO previous_period
  VISUALIZE TYPE line

Nights sold each month, stacked by channel:

FROM rooms
  SHOW room_nights_sold
  DURING this_year
  GROUP BY channel
  TIMESERIES month
  VISUALIZE TYPE stacked_area

Bar charts

Nights sold each quarter, a bar for each channel:

FROM rooms
  SHOW room_nights_sold
  DURING this_year
  GROUP BY channel
  TIMESERIES quarter
  VISUALIZE TYPE grouped_bar

Nights sold each month, stacked by channel:

FROM rooms
  SHOW room_nights_sold
  DURING this_year
  GROUP BY channel
  TIMESERIES month
  VISUALIZE TYPE stacked_bar

Each channel's part of this year's revenue:

FROM rooms
  SHOW room_revenue
  DURING this_year
  GROUP BY channel
  ORDER BY room_revenue DESC
  VISUALIZE TYPE single_stacked_bar

Part-to-whole

With more than 8 groups, the groups after the first 7 join one slice named Other. A sunburst also joins slices too thin to read into Other.

FROM revenue
  SHOW net_revenue, vat_amount
  DURING last_month
  GROUP BY revenue_category
  VISUALIZE net_revenue TYPE donut

Revenue by channel, and by room type inside each channel:

FROM rooms
  SHOW room_revenue
  DURING this_year
  GROUP BY channel, room_type
  VISUALIZE TYPE sunburst

The same query with TYPE treemap draws the two levels as tiles.

Heatmaps

Occupancy for each weekday in each month:

FROM rooms
  SHOW occupancy
  DURING this_year
  GROUP BY day_of_week
  TIMESERIES month
  VISUALIZE TYPE heatmap

Occupancy day by day:

FROM rooms
  SHOW occupancy
  DURING this_year
  TIMESERIES day
  VISUALIZE TYPE calendar_heatmap

A calendar too narrow for the whole range shows the last weeks that fit.

Distribution and relationship

Rate against occupancy for each room type, sized by revenue:

FROM rooms
  SHOW adr, occupancy, room_revenue
  DURING this_year
  GROUP BY room_type
  VISUALIZE TYPE bubble_chart

Drop room_revenue and use TYPE scatter_plot for dots of one size.

Bookings for each length of stay:

FROM bookings
  SHOW bookings
  DURING this_year
  GROUP BY length_of_stay
  ORDER BY length_of_stay
  VISUALIZE TYPE histogram

Radial

FROM rooms
  SHOW adr, occupancy, revpar
  DURING this_year
  GROUP BY room_type
  ORDER BY revpar DESC
  VISUALIZE TYPE radar

Funnel and running total

Bookings through to stays and check-ins:

FROM bookings
  SHOW bookings, stays, checked_in_stays
  ON booking_date
  DURING this_year
  VISUALIZE TYPE funnel

How each month builds the year's room revenue:

FROM rooms
  SHOW room_revenue
  DURING this_year
  TIMESERIES month
  VISUALIZE TYPE waterfall

Bar and line in one chart

Give a metric its own TYPE to draw it differently. A TYPE applies to the metric it follows and to the metrics before it that have none. A metric after the last TYPE takes that TYPE. Only bar and line mix: the chart shows as combo, with a second axis on the right when the metrics have different units. It draws one dimension at most.

FROM rooms
  SHOW room_revenue, occupancy
  DURING this_year
  TIMESERIES month
  VISUALIZE room_revenue TYPE bar, occupancy TYPE line

Any other mix stops the query:

FROM rooms
  SHOW room_revenue, occupancy
  DURING this_year
  TIMESERIES month
  VISUALIZE room_revenue TYPE bar, occupancy TYPE donut

Draw fewer points with MAX

MAX draws the first rows of the result after ORDER BY. The table under the chart keeps every row. The 8 room types with the most revenue:

FROM rooms
  SHOW room_revenue
  DURING last_90_days
  GROUP BY room_type
  COMPARE TO previous_period
  ORDER BY room_revenue DESC
  VISUALIZE TYPE horizontal_bar MAX 8

MAX takes a whole number of 1 or more:

FROM rooms
  SHOW room_revenue
  DURING last_90_days
  GROUP BY room_type
  VISUALIZE TYPE list MAX 0

The chart without VISUALIZE

Pick the metrics

VISUALIZE charts the metrics it names, which must be in SHOW. Without names, it charts the metrics in SHOW, up to the number the chart draws. A query that names metrics in VISUALIZE can leave out SHOW.

Most charts draw only the metrics that share the first metric's unit, so occupancy (a percent) and ADR (money) draw as one line. table, radar, scatter_plot, bubble_chart and funnel draw every unit. To chart two units, give one metric TYPE bar and the other TYPE line.

Nights sold and nights available as lines, without SHOW:

FROM rooms
  DURING this_quarter
  TIMESERIES week
  VISUALIZE room_nights_sold, room_nights_available TYPE line

A metric VISUALIZE names must be one the query shows:

FROM rooms
  SHOW occupancy
  DURING last_month
  VISUALIZE adr