When first day of week starts Sunday - trend report per week do not work correctly. - Known Error

When first day of week starts Sunday - trend report per week do not work correctly. Description When first day of week starts Sunday - trend report per week do not work correctly.

Steps to Reproduce

Set 2 properties in the instance to start week on Sunday glide.ui.filter.first_day_of_week Integer Value 1 glide.ui.date_format.first_day_of_week integer Value 1 https://docs.servicenow.com/bundle/rome-now-intelligence/page/use/reporting/task/modify-add-calendar-sys-props.html

Add a few time_card records Week starts on a few sundays 03/19/2023, 03/26/2023, 04/23/2023

After this either you can run the GlideAggregate scripts from scripts background (OR generate a report) STR using script:

var ga = GlideAggregate("time_card"); ga.addAggregate("COUNT", "reporting"); ga.groupBy('user'); ga.addTrend("week_starts_on", "week"); ga.query(); while (ga.next()) { gs.info(ga.getValue('timeref') + " = " + ga.getAggregate('COUNT', 'reporting')); } Observe the output on SD Tokyo and Utah instance. Utah shows the data on week before your Week starts on date.

STR to create a report Go to reports - Create a new report Table - time_card Type - Times series - Column

Config - Trend date - Week starts on Group by (optional) user Standard calendar Per - Week

Run the report - Observe how the data from 03/26 week starts on is under week 03/19 - When you drill into this, there are no matching records

Workaround Only applies to Utah+

The cause is a mismatch between properties glide.ui.date_format.first_day_of_week and behavior of function week().

Fix by setting property glide.db.week_numbering to match glide.ui.date_format.first_day_of_week:

for Sunday-based weeks use

glide.db.week_numbering = sunday_week for Monday (glide.ui.date_format.first_day_of_week=2), the value is "monday_week".

Notes:

For more details please refer to Resolution Details of PRB1540374 property glide.db.week_numbering was introduced in Utah