Open incidents by age

[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

[Victor]

@Sonali have a look on the thread below... Is that what you are looking for?

 

[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

[Victor]

@Sonali is a bit difficult/complex to explain in writing. Perhaps if I create a test widget in your instance which we can discuss it after?

[Guest Sonali]

Hi

Yeah that would be great

Sonali

[Victor]

@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...

[Victor]

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')

 

[Prathmesh Patel]

Victor, i just tried to create that but didnt work

[Victor]

@Prathmesh Patel did the SQL query (technically) not worked or it worked but the results are wrong or incorrect?

[Prathmesh Patel]

Not worked. I went in to Wigets, copied the text and selected custom.

[Prathmesh Patel]

Victor.

 

I've sorted it.

[Victor]

@Prathmesh Patel good news