I have a baq that shows the number of lines that shipped on time on a given week.
It compiles all data up to yesterday.
We had a user report they saw data for Week 29 ( July 14 - July 21)when they shouldn’t have seen it since they used the BAQ on July 15. So there shouldn’t have been any data since there were no Orders on sunday.
Is there anything I’m missing?
First, BAQ’s are inherently custom. We have no idea with the information that you presented where you are getting your data from or how you are getting it. It’s impossible for someone outside of your company to know why your BAQ may or may not be correct without seeing the BAQ.
Also,
That’s not a week, that’s more than a week. So we don’t even know how you are calculating your week numbers.
Pretend we know absolutely nothing about your issue, and explain to us what you’ve done so far to try and figure out what’s happening. After that, someone might be able to spot your logic flaw.
I’d reality check where statements, that’s quick and it’s easy to miss a condition that was written as inclusive when it should have been exclusive (ie, <= instead of <), which is the first place my mind goes when I hear this.
Second place my mind goes is to the person reporting (if indeed you’re just hearing this now!) - good job reporting that something weird happened, any chance you might be able to say something when it happens next time? Or email, or teams message, or text, send a photo, etc. Some sort of diplomatically-ish phrased version of:
May not be the issue, but I will say that if you use GETDATE() as part of your math, you may be surprised.
GETDATE() does not return the current date - it’s actually a datetime representing this moment in time. So one day before GETDATE() is not midnight yesterday, it’s exactly 86,400 seconds ago.
I went back to using the BAQ “Today” constant instead.
Note -
If you want a weekly bucket - go into the production calendar and generate a list of Weeks…
Then add a range for a year.
Viola
That’s an important distinction everywhere - always check the datatype when comparing dates and times. I’m not a huge fan of the @Today canned object - does that return the date at the company timezone or the server timezone? Regardless, it’s more exposed to change than SQL methods.
For us SaaS users, getting time at our local timezone requires a minor clunky incantation. Here’s one from my snippets file:
cast(sysdatetimeoffset() at time zone 'Pacific Standard Time' as datetime)
That’s not using something like getdate() or current_timestamp because at time zone consumes and returns a timezoneoffset. Also, hence the cast so BAQ doesn’t fall down when it sees a datatype that hasn’t been hardcoded into it. Something good to have in your pocket for anything that might potentially ever run close enough to midnight to cause an issue.
laughs in DST
cries in leap second
Well, fair enough there are risks to everything.
But if you have one server and one location all in the same time zone, and your facility operates 8-15 hours a day (not 24), and you just want your BAQ to work, @Today works just fine.
Of course, the day will come when your Epicor implementation expands to users across the country or the globe, and you’ll regret that you took my advice.
Or more likely… the person in that job after you leave will rue the BAQ you made that used @Today.
But you have to start somewhere to get the code working.