worklist.subtotals data queries run aggregate functions to categorize matching worklist records.
type:worklist.subtotals of:tickets by:[created@month,group] format:timeseries
of: key specifies the type of records to subtotal.
by: key specifies which record fields to subtotal by.
Multiple fields can be separated with commas to generated nested subtotals (e.g. “tickets by owners by status”).
The subtotal metric can be computed using different aggregate functions:
Functions target the last field specified in the
count function is the default when no preference is given, and it can be target any field type.
The other functions may only be used against numeric fields. For example, you can’t average group names, but you can average response times.
As of 9.0.7 the desired function is appended to the
by: key following a period (
In earlier versions, a separate
function: key was specified:
This still works, but is deprecated.
Histograms can be generated for date-based fields by appending a unit of time following an at sign (
@week you can optionally specify if weeks should start on Sunday or Monday. The default is Monday.
by: fields can specify
links.org) fields. This could create a report like “Sum of time tracking entries linked to organizations by month”.
When a field has only a few possible values we say it has “low cardinality”. A ticket’s status is one of four values: open, waiting, closed, or deleted. A checkbox is binary – it can only be
Conversely, some “high cardinality” fields have potentially infinite possible values. You may have millions of unique ticket subjects. There may be thousands of organizations in your address book. Your team may have hundreds of members.
You can limit the cardinality of a field by appending a tilde (
~) to any
by: field and providing a number. This only returns that number of the most common (top) values.
You can also return the least common (bottom) values by providing a negative number as the limit.
(Available in 9.0.7 or later)
metric: key lets you specify an arbitrary equation to modify the calculated value for each row in the results.
In this equation, the metric value is represented by the placeholder
Basic mathematical operations are supported using
You can group operations with parentheses (
(Available in 9.0.7 or later)
Occasionally you may need to treat nested subtotals as “samples” and calculate statistics using them.
Suppose you want the average weekly number of email replies sent per worker over the past month.
If you just use
of:message by:[worker~20,created@week] query:(created:"-1 month")
…then you’ll get back a row for every worker for every week in the past month. If there are 20 workers with 4 weekly samples, that’s 80 rows.
You can use
group: to flatten those results with a function:
of:message by:[worker~20,created@week] query:(created:"-1 month") group.avg:[worker]
This returns only a single row per worker, with the average count of their weekly samples over the past month.
The available functions are:
Like the by field, the function is appended to the
group key following a period (
The subtotaled worklist results can be returned in various formats:
tree (default) returns hierarchal data (a
valuefor each node, and a list of
dictionaries returns a table-based format suitable for sheets and API results.
categories returns a series-based format suitable for bar charts.
pie returns data for use in pie and donut charts.
table returns tabular output, suitable for display with the ‘Chart: Table’ visualization widget. When nested subtotals are used, a row is returned for each distinct result (e.g. Support -> Kina, Support -> Janey).
timeseries returns series-based data suitable for a time-series chart (with the ‘x’ axis values as timestamps).
Return a stacked bar chart of tickets by owner by status
type:worklist.subtotals of:tickets by:[owner~10,status] query:(owner.id:any) format:categories