Jump to content

Dashboard setup


JakeCarter
 Share

Recommended Posts

Afternoon all,

 

I am in the process of trying to fix a dashboard for our Service Desk and struggling to get it to show requests resolved per user 'This Month' / 'This Week'. 

image.thumb.png.c2fdae6b1288a2534beda8626dd006ae.png

 

The stats for 'Today' are working fine using the sql filter:

image.png.5aea497368e77dfb4358f931aa6eb268.png

Some filters I have used for 'This Month' (and failed) are:

image.png.2b8d3fdb7bfe9e5b39f0969deedd383e.png

image.png.d860e3cb2505cb76c7387d77767d69db.png

Any help is appreciated.

 

 

Link to comment
Share on other sites

HI @JakeCarter

Thanks for your post.  Here is some information about some of the SQL that you might be able to use.   I'm no expert and it might be that others that are more efficient with the SQL can provide a better way to achieve this, but I thought that this would give you something to look at.

The h_dateresolved field holds both date and time in the format 2022-09-27 20:44:41.  You will need to keep this in mind when working with date functions as some will only return the date, and not the time.

When comparing h_dateresolved to curdate() you need to consider that the value stored in h_dateresoved will be something like 20220927204441 (2022-09-27 20:44:41) where as curdate()-30 will return 20220897 which isn't a valid date. Also, when comparing the values, you can see which one is always going to be greater than the other.

Today's Requests

One way to get the date, excluding the time from the h_dateresovled field is using  cast(h_dateresolved as DATE).   This will remove the time from the end and just look at the date. You can then make the comparison cast(h_dateresolved as DATE) = curdate() to filter on requests resolved today.

This Month's Requests

If you are looking for requests resolved in the current month you need to do a match between the month and year of the request against the current month and the current year. If you try looking for - 30 days then you can lead into the previous month.  There is also the challenge of different months containing a different number of days.

That may look something like: month(cast(h_dateresolved as DATE)) = month(curdate()) and year(cast(h_dateresolved as DATE)) = year(curdate())

Let us know if that helps (and if it works). 

image.png

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
 Share

×
×
  • Create New...