Skip to main content

Number of tickets created this year month by month compared to last year - Tudásbázis / Developer & Reporting / DPQL Examples - Deskpro Support

Number of tickets created this year month by month compared to last year

Authors list

It could be useful to create a report that compares the number of tickets created this year month by month as compared to last year. 

To do this, the first part is to generate a report with the number of tickets created monthly for this year, which can be done using the following DPQL format:

SELECT DPQL_COUNT() AS 'Number of tickets created this year'

FROM tickets

WHERE tickets.date_created = %THIS_YEAR%

GROUP BY DATE_FORMAT(tickets.date_created, '%M') AS 'Month'

ORDER BY tickets.date_created


The second part is to bring in data from last year to compare to this year. This can be done using the LAYER WITH clause, which allows you to combine two different queries into one. The final DPQL is written as below:

SELECT DPQL_COUNT() AS 'Number of tickets created this year'

FROM tickets

WHERE tickets.date_created = %THIS_YEAR%

GROUP BY DATE_FORMAT(tickets.date_created, '%M') AS 'Month'

ORDER BY tickets.date_created

LAYER WITH

SELECT DPQL_COUNT() AS 'Number of tickets created last year'

FROM tickets

WHERE tickets.date_created = %LAST_YEAR%

GROUP BY DATE_FORMAT(tickets.date_created, '%M') AS 'Month'

ORDER BY tickets.date_created


When this is first created, the default display will be in a 'Table' form. In this case, it would be useful to have it in a 'Lines' and 'Bars' display, to have a visual representation of the data. To customise the form of content displayed, go to the 'Display' drop-down list that is located on the just below the title.

The resulting display will be as shown below:



Hasznos Nem hasznos

5 felhasználóból 4 hasznosnak találta ezt a cikket.

Kérjük ebbe a mezőbe írja hozzászólását, majd kattintson a 'Hozzászólás beküldése' gombra...

Please log in or register to submit a comment.

Need a password reminder?