跳到主要内容

Number of tickets created this year month by month compared to last year - 知识库 / Deskpro Legacy - Deskpro Support

WARNING: You're browsing documentation for an older version of Deskpro. For the most up-to-date version, see: Number of Tickets created this year month by month compared to last year

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

作者列表

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:



有帮助 没有帮助

5 人中 4 人认为这个页面有帮助

添加一条评论

请登录或注册以提交评论。

是否需要密码提醒?