Column Chart showing opened and closed incidents per day

Stephane_Bouillon

Hi, I'm trying to create a chart that visualizes wether our backlog is growing or shrinking over time. I start by showing the
number of opened and closed incidents per day for the last 60 days. I know how to do it for Opened and Closed in different charts, but I want them overlaid in the same chart. I also want to create a chart that shows the number of non-closed incidents on that day (our backlog).

This is what I have so far:

SELECT CONVERT(Date,ClosedDate) as Closed<br>&nbsp;&nbsp;&nbsp;FROM WorkItem wi<br>&nbsp;&nbsp;&nbsp;WHERE DATEDIFF(dd,ClosedDate, Getdate()) <= 60<br>   AND wi.ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68'<br>   AND ClosedDate > 0

SELECT CONVERT(Date,Created) as Opened<br>&nbsp;&nbsp;&nbsp;FROM WorkItem wi<br>&nbsp;&nbsp;&nbsp;WHERE DATEDIFF(dd,Created, Getdate()) <= 60 AND wi.ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68'

I group by Closed and Opened respectively.

Stephane

Brian_Wiest

Versions 8.0 (or maybe 7.0+) has Cireson analytics included. If you review those dashboards there is one call Work Item trend.
The Nice thing about analytics is you can edit the dashboard to pull what Cireson SQL statement to build the widget. That should get you started in building similar widgets for your business cases. 

Stephane_Bouillon

Thanks for your time and effort Brian,

The Dashboard you refer to is also showing the Opened and Closed items in different charts, which is why I wanted to create my own...

Stephane_Bouillon

I have the SQL Table Widget query sorted out, see https://community.cireson.com/discussion/3363/get-opened-and-closed-workitems-per-day#latest but I can't figure out the SQL Chart Widget chart type and parameters to visualize it the way I want.