How to report on "Day of Week" for date fields?

[#1]

Our IT Director has requested the ability to report on number of Incidents opened and closed on particular days of the week. He'd like to use this info for purposes of planning staffing for our help desk.

Does anyone know of an easy way to do this? The best plan I can come up with is to created a calculated field to translate the "Opened" and "Closed" fields into date or integer values, do some division, and return a String or maybe a Day of Week value based on the remainder. But, this seems convoluted, and so far I'm having trouble getting the "Opened" and "Closed" Datetime fields into a format I could do the math on. I also tried using the general Javascript Date method getDay() in the field calculation, but I haven't had luck getting that to work either.

Any suggestions would be appreciated!

[brozi]

Not sure if this meets your needs, but have you looked at using a trend chart, trend field opened per day? Take a look at demo today (5/25) report called: Incidents opened trend by Day

This only one of the trends, so you would have to do two reports.

Pic

[#3]

Thanks, Ian. That will get us much of the way there, and is what we actually ended up doing. I ended up abandoning building the original request -- for a Pivot table displaying week-over-week comparison of Incidents opened/closed by day of week -- on the basis that it was taking more time than it was worth, but I'll share the work I began towards being able to report in that specific format on in case it might prove helpful to someone else.

My plan was to create two fields, "Day of Week Opened" -- to return 'Monday','Tuesday', etc. -- and "Week Opened" -- to return an integer to represent a particular week. I didn't actually build the second field out, but my intention was to have it return a simple count of weeks since 1/5/70 (the first Monday since 1/1/70). I planned to then use these two fields as the columns and rows of a pivot chart with count in the cells.

I did finish building the "Day of Week Opened" field (formula below). Testing indicated that it was working pretty well overall, but in some cases, date/time values between midnight and 2am were incorrectly returning the prior day of the week. I'm guessing this might have had something to do with daylight savings time, but wasn't able to confirm that.

That's as far as I got! -Martha


Formula for "Day of Week" field:

//find diff betw 1/5/70 & today in days
//1/5/70 chosen somewhat arbitrarily as first Monday after 1/1/70
var diffInDays = Math.floor(gs.dateDiff(gs.dateGenerate('1970-01-05','00:00:00'),current.opened_at,true)/(60*60*24));

//divide by 7 and find remainder
var remainder = Math.floor(diffInDays)/7-Math.floor(Math.floor(diffInDays)/7);

//set day of week according to remainder
if (remainder >= 6/7-0.1) {
'Sunday';
} else if (remainder >=5/7-0.1) {
'Saturday';
} else if (remainder >=4/7-0.1) {
'Friday';
} else if (remainder >=3/7-0.1) {
'Thursday';
} else if (remainder >=2/7-0.1) {
'Wednesday';
} else if (remainder >=1/7-0.1) {
'Tuesday';
} else {
'Monday';
}

[john_roberts]

Here's a shortcut for day of week, it will also take the users (whoever updates the field) timezone into account.
"gr" is a GlideRecord, could also use current if in a business rule.
gr.sys_created_on.getGlideObject().getDayOfWeek()
This will work on any GlideDate or GlideDateTime field.
It returns an integer, Monday=1, Sunday=7.

[#5]

Awesome! I was looking for something like that but couldn't find it. Thanks, John!