WITH

Use WITH to apply optional modifiers, which are keywords that add result columns or change how ShopifyQL calculates results. Modifiers can add generated result columns, change currency and timezone calculations, or calculate cumulative values.

Use one WITH clause for each query. To apply more than one modifier, separate modifiers with commas in the same WITH clause.

Some WITH modifiers add generated result columns in addition to the columns that SHOW returns. ShopifyQL bases each generated column name on the metric name that the query returns. If a field in SHOW uses AS, then ShopifyQL uses the alias in the generated column name.

You can use generated result columns in clauses that operate on returned values, such as ORDER BY and VISUALIZE, when the columns are valid for the query.

Anchor to Total columnsTotal columns

WITH TOTALS adds a total column for each metric, aggregated across the entire result set, when a query groups results with GROUP BY or TIMESERIES. ShopifyQL sums additive metrics like net_sales, and recomputes ratio and percentage metrics, such as average_order_value, over the full result set instead of summing the grouped rows. ShopifyQL adds the __totals suffix to each metric name:

• net_sales__totals is the totals column generated for net_sales.

Anchor to Group total columnsGroup total columns

WITH GROUP_TOTALS adds subtotal columns when a query groups by at least two dimensions. ShopifyQL generates a subtotal column for each prefix of the grouped dimensions except the full set, so grouping by three dimensions adds two subtotal columns. ShopifyQL names each column after the metric and the dimensions in its prefix:

• Grouping by two dimensions adds one column, {metric_name}__{first_dimension}_totals, such as total_sales__country_totals for the country subtotal of total_sales.
• Grouping by three dimensions adds two columns, {metric_name}__{first_dimension}_totals and {metric_name}__{first_dimension}_{second_dimension}_totals, in the order of the grouped dimensions.

Anchor to Percent-change columnsPercent-change columns

When the reporting period runs up to now or today, ShopifyQL compares each period only through the same point in its grain, so a partial current period is measured against an equal period-to-date instead of against a full prior period. For example, on the 17th of a month, each comparison month is measured through its own 17th.

WITH PERCENT_CHANGE adds columns for date comparison metrics created by COMPARE TO. Without a date comparison, this modifier doesn't add percent-change result columns. ShopifyQL names each column after the compared metric and the comparison period:

• A relative comparison, such as COMPARE TO previous_year, generates percent_change_net_sales__previous_year.
• A date-function comparison generates a column such as percent_change_total_sales__startOfQuarter_sub_3q, where minus signs in the date function become sub.

WITH CUMULATIVE_VALUES adds a running-total column for each additive metric, where summing values across time is meaningful, such as sums and counts like net_sales, orders, and customers. Ratio and average metrics, such as average_order_value or conversion_rate, don't generate cumulative columns.

Cumulative values require a time-based order, so combine CUMULATIVE_VALUES with TIMESERIES or ORDER BY on a time dimension. Reference a generated running total by the metric's __cumulative column, or by its alias if the metric is renamed with AS.

Use WITH CURRENCY '<currency_code>' to run currency calculations using a valid currency code. Use WITH TIMEZONE '<iana_timezone>' to run time-based calculations using a valid IANA timezone. Both values are strings, so wrap them in single quotes.