If I'm running a report with the filter:
Issue Actual Completion Day Greater than or Equal to $$TODAY-3d
on a Monday morning why would I not pull in any results from Friday? When I change to $$TODAY-4d I get results so I know there's data there. I know it's counting the weekend days but that doesn't explain why it wouldn't pull anything in since Friday is technically 3 days back from Monday. I appreciate any insight. Thanks!
$$TODAY can be a frustrating wildcard. I had to submit a ticket for this before.
Apparently $$TODAY is 12:00am the date of your current time zone. As soon as you do a calculation in conjunction with $$TODAY, it disregards your current time zone and becomes UTC. You can verify this by adding 2 columns into your report - one with valueexpression=$$TODAY and one with valueexpression=$$TODAY-3d. I did it and in my screenshot you can see $$TODAY is 11/15/21 12:00AM but as soon as I add "-3d" the time becomes 4:00PM.
We had a report where we needed anything flagged that was submitted after 9am, so we had to set our calculation to $$TODAY+16h to account for the UTC conversion. Then when we set our clocks back an hour last week (we're in the US), we had to adjust the formula to be $$TODAY+17h.
Sorry, I misspoke - it's not UTC - it's GMT. I just rummaged through my support tickets and found the response I had received from Engineering:
"We looked into this issue, and it looks like it is always basing the time off of the GMT timezone and not the local timezone, but when you just do $$TODAY it removes the timezone and so it will show midnight. However, once you start to shift the hours it is taking into account the timezone, which is based on the GMT time. This is how the system has always worked and isn't something we would change as an issue fix."
Regardless, $$TODAY wildcards have caused a handful of grey hairs for me.