Trend report per week is showing incorrect data and bars

[Mahesh Kumar2]

I am trying to create a trend report on incident_sla table

trend by - closed -> per week for count of records

I am trying to create a report for below data(this is for reference)

this data is of 7 days which spanned between 2 weeks. So i should get some data under previous week and some other on current week in trend report as bars. But i am getting bars for current week and next week which is incorrect. Below screenshot is for the reference of report.

In above report, 2024-01-07 bar is saying 11 records but actual records in that week are 15, when i click on that bar. I will be redirecting to the correct data of 15 records as below.

and there is a bar for future week 2023-01-14 is showing 12 records in report, ideally there is no data (incidents closed) for future week, if i click on that -> no data is available. Please refer below screen shot.

And some data is available for previous week (you can refer first screenshot), but there is no bar for 2023-12-31 to show previous week data. 11+12 = 23. Total number records are correct. But distribution and representation is incorrect in trend report. Can some body help on this.

[JaeggerLegane]

I have the impression that it shows 14/01 on your graph, because Sundays are considered the 1st day of the week on your instance. Therefore, if a record was closed yesterday, it will visualize them on the bar of 14/01.

Could you check the following:
1. Filter navigator -> sys_properties.list
2. Search for property "glide.ui.date_picker.first_day_of_week"

If you want Mondays to be the first day of the week, you'll need to change the value to 2.
Then the bars will correctly reflect in your reports.

Note that this will affect other areas of the platform so you may want to reconsider before making this change.

[Mahesh Kumar2]

Hi  JaeggerLegane

Thanks for the response. I think the issue is same as you mentioned. But i didn't find the above property in my instance. But i have gone through other posts and found one more similar property : glide.ui.date_format.first_day_of_week 

If i change the value from 1 to 2, it is considering Monday is starting day of the week, showing correct data with Monday dates on x-axis.

But thoughts on this is like, why ServiceNow trend showing incorrect visualization as it is showing  count on next week date but if we click the bar -> it will select the 7 days span of next week and no data exists. Ideally this is wrong. Is there any limitation from ServiceNow on this part?

[Michael Ramos]

We are having a similar issue as this post. We had intentionally set the following properties to be 1 because we operate 24/7/365 and consider Sunday as the start of the week.

glide.ui.date_picker.first_day_of_week

glide.ui.filter.first_day_of_week

glide.ui.date_format.first_day_of_week

We also set this property to False since ISO weeks start on Monday.

glide.db.aggregates.trend.use_iso_week

That said we have the same result. The bars still reflect data as if Monday was the start of the week. So in Mahesh's example, the bar for 1/14/24 is actually containing data from 1/8 to 1/14 instead of 1/14 to 1/20. When you click on the bar to view the data, the list query you get is what you would expect but the report doesn't reflect the same data.

I would be interested in a solution to this issue that allows the day of the week to remain as Sunday and for the report to show the accurate data. 

[Michael Ramos]

So for anyone still struggling with this issue, we opened a ticket with ServiceNOW Support and they have an Internal KBA around this. 

This was the information they provided from that KBA.

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

---

We did end up creating the property glide.db.week_numbering and set a string value of sunday_week

This fixed the issue and the trend reports now work as expected.

To recap all the properties we have set to have Sundays as the start of the week:

glide.ui.filter.first_day_of_week = 1
glide.ui.date_format.first_day_of_week = 1
glide.db.aggregates.trend.use_iso_week = false

glide.db.week_numbering = sunday_week

Note that glide.db.week_numbering has the following options and having Sunday or Monday as the start weeks are the recommended and officially supported according to this:

https://docs.servicenow.com/bundle/xanadu-now-intelligence/page/use/reporting/task/modify-add-calend...

iso_week	Week 1 is the week with January 4, the first business day, or the first Thursday in it. (These are mutually equivalent.) There is no Week 00.
sunday_week	Week 1 is the week with the first Sunday of the year. Days before it are in Week 0.
monday_week	Week 1 is the week with the first Monday of the year. Days before it are in Week 0.
jan1_week	Week 1 is Jan 1-7; the next seven days are week 2, and so on.