Solved

Returning data from Yesterday

  • 1 November 2022
  • 6 replies
  • 142 views
D
Rank
Userlevel 4
Badge
  • Del
  • Freshman I
  • 39 replies

I have a Scorecard on a dashboard that show ordered that were invoiced yesterday. 

In the parameters for the scorecard I have Created On is equal to @Today-1.

This has been working until today (November 1, 2022). This morning the score card shows 0 invoices created yesterday, when there was one created. If I change the parameter to Created On is equal to 10/31/2022 the score card shows 1 which is correct.

Is there some issue with @Today-1 when at the beginning of a new month, or is there a better may to automatically pull yesterday from the score card parameter setting?

icon

Best answer by BenjaminCrisman 9 November 2022, 18:11

View original

6 replies

meganfriesen37
Rank
Userlevel 7
Badge +10

I’ve encountered similar errors due to the time zone that a user was in and how dates tend to be stored in the system. 

  • So a document might be stored as 11/01/2022 12:00:00 AM EST which would be equal to 10/31/2022 10:00:00 PM MST.  (2 different calendar days)
  • I usually encounter this with time activity data, and if I want data for Oct 31 I can’t just do “Date = Oct 31”, I need to include everything between 12:00AM and 11:59PM (which usually involves some fun date shifting formulas, which I think I’ve posted about on another post).

It is a bit weird though that it’s still working when you hard code in the date though...

Megan Friesen, Contego Software Solutions, Canada
D
Rank
Userlevel 4
Badge

meganfriesen37

Thank you for the reply. This isn’t a time zone issue, we are all in the same facility and time zone.

Your reply is good information that I will make note of for any future issues we might come across.

M
Rank
Userlevel 1

We are having a similar issue with a KPI widget.  When we use “created on date” and @today-1 we get zero results, when we use “date” it populates but sometimes includes created on dates other than yesterday.  When we use yesterdays date it populates properly as well.

BenjaminCrisman
Rank
Userlevel 7
Badge +4

Hi @Del! One thing to keep in mind is that all data stored in SQL will be in UTC time, which can differ greatly depending on where you are in the world.

So if a document created at 9pm PST on 10/31/2022, it will be stored in the database with a date of 11/01 and the UI will convert it to the time zone of the user when applicable.

If you are using an expression to calculate the date it will always use the time in SQL, but if you just add the date field to a GI the UI will try to show the date relative to the user.

An easy way to test this is to open the GI and add a new column for the date, but instead of using the date field, just use the date field in expression format, like:

=[ARInvoice.CreatedDateTime]

This will show you what is stored in the DB

Does this sound like what you are experiencing?

D
Rank
Userlevel 4
Badge

BenjaminCrisman

I create the column as you suggested and ran the GI. Looking at the results the date in the new column is the same as the date we are using in the Tile with the parameter of @Today-1. 

This worked fine until November 1st when the GI returned no data for October 31.

On November 2 the GI displayed data from November 1st as expected and it continues to work each day since.

It seems that on November 1st the GI wasn’t able to figure out that is should pull data from October 31 

Thank you for your input.

BenjaminCrisman
Rank
Userlevel 7
Badge +4

@Del that is strange. If you are still interested in checking the issue you could try changing the business date back to 11/1 and see if the issue persists:

I haven’t been able to replicate anything like it locally though, even from 11/1, so if changing the date doesn’t replicate then I would be hard pressed to know what the root cause was.

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved