Jump to content

Burn Rate Stat on Wallboard


Michael Sharp

Recommended Posts

Hi all, I currently have a statistic on my wallboard that indicates tickets opened and tickets closed in a day.  I would like assistance to replace this with a "Request Burn Rate" statistic if possible i.e. if we have opened 5 more than we have closed the statistic would show "+5" (conditional format would also turn this red as a negative impact to the desk).

Regards,

Mike.

Link to comment
Share on other sites

  • 2 weeks later...
  • 3 weeks later...

@Michael Sharp

Try this as a starter in a widget (list of data > custom SQL) 

 

select
(SELECT COUNT(*) AS 'Logged' FROM h_itsm_requests WHERE h_datelogged >= CURRENT_DATE) - (SELECT COUNT(*) AS 'Resolved' FROM h_itsm_requests WHERE h_dateresolved >= CURRENT_DATE) AS 'Burn Rate'

The last AS is the title shown in the widget. Obviously this will show today's numbers. You would need to add some date queries instead of current date for burn rates over a week or month.

Last month would be (I believe):

h_dateresolved >= LAST_DAY(NOW() - INTERVAL 2 MONTH) + INTERVAL 1 DAY


This week:

h_dateresolved >= (CURDATE() - INTERVAL (WEEKDAY(CURDATE())) DAY) 


Last 7 days:

DATE_SUB(NOW(), INTERVAL 7 DAY)>= h_dateresolved

@Victor fact check please? Also I am sure you have an easier way somewhere.

Link to comment
Share on other sites

  • 2 weeks later...
On 11/2/2018 at 4:06 PM, Dan Munns said:

@Michael Sharp

Try this as a starter in a widget (list of data > custom SQL) 

 


select
(SELECT COUNT(*) AS 'Logged' FROM h_itsm_requests WHERE h_datelogged >= CURRENT_DATE) - (SELECT COUNT(*) AS 'Resolved' FROM h_itsm_requests WHERE h_dateresolved >= CURRENT_DATE) AS 'Burn Rate'

The last AS is the title shown in the widget. Obviously this will show today's numbers. You would need to add some date queries instead of current date for burn rates over a week or month.

Last month would be (I believe):


h_dateresolved >= LAST_DAY(NOW() - INTERVAL 2 MONTH) + INTERVAL 1 DAY


This week:


h_dateresolved >= (CURDATE() - INTERVAL (WEEKDAY(CURDATE())) DAY) 


Last 7 days:


DATE_SUB(NOW(), INTERVAL 7 DAY)>= h_dateresolved

@Victor fact check please? Also I am sure you have an easier way somewhere.

How would I use scorecards with this please?  I would like the daily burn rate for the week (Mon-Fri)

Edited by Michael Sharp
Additional detail
Link to comment
Share on other sites

@Michael Sharp sorry I don’t understand what you mean by the daily run rate for the week. 

You want to have a list of counters widget with Monday to Friday as a list?

The widget will have to be a custom sql widget as you are using aliases and the drop down list way of making widgets won’t allow you to do that.

Realistically I think you are going to be looking at a ‘burn rate for today’ widget and then a ‘burn rate for the week’ widget using the different date values above.

Link to comment
Share on other sites

On 11/16/2018 at 12:13 AM, Dan Munns said:

@Michael Sharp sorry I don’t understand what you mean by the daily run rate for the week. 

You want to have a list of counters widget with Monday to Friday as a list?

The widget will have to be a custom sql widget as you are using aliases and the drop down list way of making widgets won’t allow you to do that.

Realistically I think you are going to be looking at a ‘burn rate for today’ widget and then a ‘burn rate for the week’ widget using the different date values above.

Hi Dan,

I want a burn rate displayed as daily scorecards on my measures page so I can see sparklines etc.  So I can see the burn rate per day over a set amount of time?

Regards and thanks,

Mike.

Link to comment
Share on other sites

  • 1 year later...

@Dan Munns @Bob Dickinson @Victor please can you advise if I can use Burn Rate on my scorecards in analytics please?  I want to be able to see opened vs. closed over a length of time (ideally without having to compare open/close) as I can colour code the scorecards according to growth/break even/reduction in volume.  The widget works well but this is a point in time for that day.

Regards,

Mike.

Link to comment
Share on other sites

@Michael Sharp

The SQL @Dan Munns has provided can give you the data on a widget, which can different occurrences of it could be used to give you different periods, but I do not believe it is possible currently to get a 'measure' to capture the difference between two related counts, i.e. value=count of new requests - count of closed requests for the measure's sample period.  The measure is required in order to be able to use the scorecard/sparkline output.

This 'Burn Rate' is something we are trying to monitor, so like yourself having the ability to create a more complex SQL measure inserting date periods is something we would want and I believe we may have raised before.

Cheers

Martyn

 

Link to comment
Share on other sites

Hi @Michael Sharp

I've had a look at this, and I'm not sure it's possible to achieve exactly what you are looking for in the form of a Scorecard. I'm just investigating if there is anything clever we can do using a combination of the "Count" option and the WHERE clause and will get back to you. 

Kind Regards

Bob

Link to comment
Share on other sites

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
×
×
  • Create New...