Relative field not working as expected

[Thereza Van der]

Good afternoon all 🙂

I would like your input please. I have a report that is supposed to show all changes from a Tuesday till the following Monday. If I use the relative field it does not work as expected.

The first part is working (relative after 5 days) so it always starts with the changes for the Tuesday, but I am battling with the second half. If I say 'relative before 13 days ahead', it misses one change and if I say 'relative before 14 days ahead' it adds two changes for the next Tuesday. I know 14 days is incorrect, because I want 7 days of data (Tuesday till Monday) and 14 minus 6 is 8. But why does 'relative before 13 days' not pick up the one change? I attach screenshots for your convenience. I really hope someone can show me what the problem is.

[Mark Manders]

It doesn't look at dates, it looks at milliseconds. So the query translated to milli seconds and checks on the date/time field you are looking at, also in milliseconds. It could very well be that you see something different in the morning than you see in the afternoon.

[Thereza Van der]

Morning Mark

Thanks, is there any other way to automate my report that is sent out before the CAB meeting? It creates a problem when it is different than the actual changes in the Workbench.

[Mark Manders]

Why do you need to create a report, when all actual information is in the Workbench? 

But if it's automated, I assume you have it as a scheduled report? That will run every week on the exact same time. Run it at 00:01 it will always collect the same data at that same moment. 
So while you are looking at it during the day, when setting it up, you may run into the different times a change is starting (a 10AM change is showing when you look at the report at 11AM, but the 12PM isn't there yet). At 00:01 the 11AM also wouldn't be showing.

[Thereza Van der]

This is the setup:

We have a CAB meeting every Tuesday at 09h00. People have time until end of business on the previous Wednesday to submit their changes. On Wednesday at 23h59 my scheduled report runs and is distributed to all to make sure that their changes are on the list for Tuesday's CAB meeting. My scheduled report's filter is: