Dashboard setup

[JakeCarter]

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

 

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

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

Any help is appreciated.

 

 

[James Ainsworth]

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

[James Ainsworth]

And here is a filter that may work for a This Week series

week(cast(h_dateresolved as DATE)) = week(curdate()) and year(cast(h_dateresolved as DATE)) = year(curdate())

[JakeCarter]

Hi @James Ainsworth

 

You are a legend as always.

 

This has worked wonderfully. I really appreciate the breakdown as this will help with some other bits we are thinking about doing.