LAYER WITH allows you to combine two different queries into one. For example:

SELECT
    DPQL_COUNT() AS 'Open Tickets',
    tickets.organization.name AS 'Department',
    'Tickets' AS 'value_axis_title'
FROM tickets
WHERE
    tickets.organization.name <> NULL
    AND tickets.status IN ('awaiting_user', 'awaiting_agent')
GROUP BY tickets.organization.name AS 'Department'

LAYER WITH

SELECT
    DPQL_COUNT() AS 'Closed Tickets',
    tickets.organization.name AS 'Department'
FROM tickets
WHERE
    tickets.organization.name <> NULL
    AND tickets.status = 'resolved'
GROUP BY tickets.organization.name AS 'Department'

This will send two different sets of data to the chart, and charts them both against the same canvas. The above query and the chart below show open tickets against resolved tickets across departments.

image.png

Published: 23/03/2018

Last updated: 22/05/2018