Jump to content

Open incidents by age


Guest Sonali
 Share

Recommended Posts

Guest Sonali

Hi 

I want to create a pie chart showing the % of open incidents by age (seven or less days open; greater than 7 but less than 30 days and greater than 30 days)

I am not sure how to go about creating this. 

Please can you help me

Sonali

Link to comment
Share on other sites

Guest Sonali

Yes this is what I am looking for. I can't see the logic behind to how it was created on the thread

Link to comment
Share on other sites

@Sonali I had a play around with widgets and measures and the best what I can come up with is a widget as per below (this is from your instance). I can't do a (pie) chart type because I can't use current pie chart functionality to get the data I need to do all the % grouping and calculations... besides, pie chart requires measures and measures can't give you current period data...

Open_Incidents_Age.PNG

Link to comment
Share on other sites

For anyone interested, the above widget can be created using a custom SQL query like this one:

SELECT 
    ROUND(SUM(CASE
        WHEN DATEDIFF(NOW(), h_datelogged) <= 7 THEN 1
        ELSE 0
    END)*100/COUNT(*),0) AS '0-7 Days (%)',
    ROUND(SUM(CASE
        WHEN DATEDIFF(NOW(), h_datelogged) BETWEEN 8 AND 29 THEN 1
        ELSE 0
    END)*100/COUNT(*),0) AS '8-29 Days (%)',
    ROUND(SUM(CASE
       WHEN DATEDIFF(NOW(), h_datelogged) >= 30 THEN 1
        ELSE 0
    END)*100/COUNT(*),0) AS '30+ Days (%)'
FROM h_itsm_requests
WHERE h_status IN ('status.open', 'status.onhold', 'status.resolved')

 

Link to comment
Share on other sites

  • 4 months later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...