Issue with analytics

[Dan Munns]

Hi all,

I am trying to have a live dashboard to display in the service desk with stats such as:

Number of tickets open - total

Number of tickets logged - daily

Number of tickets open - per analyst

Number of tickets breached - total

However it seems that the measures will only rescan once per day so all of the widgets are a day behind.

I have a widget set up to show open tickets (attached) and even though it seems incorrect (47 open tickets, I can only see 38 in service manager) it has captured the three I logged yesterday, just a day behind.

I am certain it is something I have done (or not done) as is usually the case.

Any ideas?

Thanks

Dan

[Victor]

@Dan Munns I assume the img with "47" is a widget... if a widget is using a measure then yes, you will have previous period records (in this case since period is "daily", you will have yesterday numbers). However if the widget is using an SQL query then this will have real time values....

 

(thanks to @Steven Boardman for this, I had no idea how to get real time numbers on widgets )

[Dan Munns]

@Victor So there is no way for me to create the attached dashboard?

The only way I can see you can specify a SQL query is to use 'data chart' from the widgets creation menu (which is either a bar or pie chart). However the attached image would seem to be target counters. The names of at least 4 of the 5 would suggest to me that they are live numbers. So has this functionality been removed or am I just looking in the wrong place?

Thanks

Dan

[Dan Munns]

@Victor Ok cancel my last.

I feel that I should have a special "I have found what I was looking for after moaning I couldn't find it" button.

The only issue I have now is the fact that it seems to be showing tickets which I cannot find anywhere. As far as I can see in the request list we have 40 requests of all types. But the widget is showing as attached.

Any ideas where they may be hiding? I am a member of all teams and services but still they are incorrect. If it helps it is just the service requests which is wrong. Incidents are correct at 15.

Thanks (for your enduring patience mainly)

Dan

[Steven Boardman]

@Dan Munns

Could you share your query which sits behind this widget?

Are you specifically referencing the statuses to include or exclude?  have you factored in any cancelled statuses which might not show on the request list view?

Hopefully your query will shed some light?

Steve

[Dan Munns]

hi @Steven Boardman

I have attached the SQL query. I am targeting only certain statuses and request types but I am very new to this whole SQL game so may have done something wrong. I did wonder if it was because we have done a clear down just before Christmas and when I spoke to Bob Dickinson he did mention that when I do the clear down for go live I need to ask someone at Hornbill to do something that the clear down app doesn't yet do so I wondered if it was anything to do with that?

Thanks

Dan

[Victor]

@Dan Munns first try set up the WHERE clause like this:
(h_status = 'status.new' OR h_status = 'status.new' OR h_status = 'status.new') AND (h_requesttype = 'Incident' OR h_requesttype = 'Service Request')

Brackets are important when combining AND and OR operators...

 

If it still shows incorrect numbers try isolate the culprit by running each criteria independently and make a note of the numbers for each criteria like all requests (incidents + service requests with status new... then incidents + service requests with status open, etc.) and compare with what you see in the request list...

[Dan Munns]

@Victor it would appear I owe you a beer.

I will add it to the tab.

 

[Victor]

@Dan Munns uh... I have a tab open with you....!!! Thanks! ...right... ermm... 

(*mumble to myself: what do I need, what do I need...ermm.. could use a new gaming machine, that, mkay...maybe a bigger TV...mkay...*continues to mumble unintelligible...)

[Dan Munns]

Ok so I now have 2 pages of widgets for a wallboard screen. I am now trying to create a widget to show resolved calls per analyst per week. I can set it to show the last X days but as this is a rolling X days it isn't what I need. I need it just to show all calls resolved since the Monday to CURRENT_DAY but cant work it out. The CURRENT_WEEK bit is where I need a hand

Any help @Victor? Might get you a step closer to the cuddly toy... (I am sure the TV will come eventually )

 

[Kelvin]

I may be wrong but the syntax looks slightly wrong. I thought you needed brackets around each criteria 

(h_requesttype = 'Service Request') OR (h_requesttype = 'Incident') AND ...

[Dan Munns]

This syntax works for all the other widgets (thanks Victor)

I believe you can bunch OR together but have to specify groups if using AND

(SQL Newbie so I am sure I will be corrected shortly  )

[Kelvin]

@Dan MunnsI think you probably know more SQL than me

I wish I knew more..

Im sure Victor will give us the answer.

[Victor]

@Kelvin @Dan Munns it depends on how you want the expression to be evaluated... you would need to be aware of logical operators precedence as AND and OR have different levels of precedence... To make a simple comparison think of arithmetic operations for multiply or, let's say, addition/subtraction. Example: 2 + 4 x 3 -1 equates to 2 + (4 x 3) - 1 which = 13 - you would not need the brackets because of multiply precedence. However if you want the addition to evaluate first then you would do (2 + 4) x 3 -1 = 17. So you can see you will get different results depending on how you configure operators precedence using brackets.

Going back to your example, if you put (h_requesttype = 'Service Request') OR (h_requesttype = 'Incident') AND (<more_citeria>) then the first expression that evaluates is: (h_requesttype = 'Incident') AND (<more_citeria>) - because AND has a higher precedence than OR. The result of this expression, let's call it <result_1>, is used for the next evaluation: (h_requesttype = 'Service Request') OR <result_1>.

Hope this makes sense

 

 

[Dan Munns]

@Victor Ok that makes some sense. Looks like I will be reading up on SQL in my time off (haha)

Getting back to my original issue (today anyway) any help with the current week part?

[Victor]

@Dan Munns sorry, missed that part... so much for my new TV  ...

Try this: (  h_dateresolved > (CURDATE() - INTERVAL (WEEKDAY(CURDATE())) DAY)  )

[Dan Munns]

Yet again @Victor I am in your debt.

Thanks

Dan