Solved

Display First Date of Last Week on Report Designer

  • 5 September 2023
  • 6 replies
  • 123 views
K
Rank
Userlevel 4
Badge

Hi,

We would like acumatica to display the first date of a week. For instance: Today it should show 08/28/2023. How can we achieve this?

We tried : DateAdd(Today(),'w',-3) and it displays Tuesday’s Date 08/29/23 and not 08/28/23.

What would be the best way to do this? I have this formula working in SQL :DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE())-1,0) but cannot figure out how to replicate in acumatica. 

 

@lauraj46 - Hi Laura, could you please help when you have a chance?

icon

Best answer by lauraj46 6 September 2023, 16:05

View original
Kaavya

6 replies

lauraj46
Rank
Userlevel 7
Badge +7

Hi @kanupindi ,

Try this formula:

=DateAdd('1/1/1900', 'w', DateDiff('w', '1/1/1900', Today())-1)

Laura

 

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
K
Rank
Userlevel 4
Badge

Hi @lauraj46, thank you for your swift response as always! I tried this on the GI and seemed to work. I still need to check the report designer as well. With this formula, would the dates change dynamically?

Kaavya
lauraj46
Rank
Userlevel 7
Badge +7

Hi @kanupindi ,

Glad that worked!  It will change dynamically based on the current date (Today() ).

Laura

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
lauraj46
Rank
Userlevel 7
Badge +7

Hi @kanupindi ,

There seem to be some idiosyncracies with the date function in report designer.  You can use CDate to cast 1/1/1900 as a date, but I had to adjust the first date forward to 5/7/1900 to get the correct date as an end result.  Not sure why it should be 5/7/1900, but this formula does seem to work:

=DateAdd(CDate('5/7/1900'), 'w', DateDiff('w', CDate('1/1/1900'), CDate(Today()))-1)

A different approach is to build the formula based on the day of the week.  The idea is to subtract a certain number of days based on the day of the week.

Beware though.  In my experiments the DayOfWeek function operates differently in a GI vs the Report Designer.  In a GI the DayOfWeek function returns 1-7 beginning with Sunday.  In the Report Designer, the DayOfWeek function appears to return 0-6 beginning with Monday.  

In the Report Designer, I was able to get the correct behavior using this formula:

=IIf(DayOfWeek(Today())=0, DateAdd(Today(), 'd', -13), DateAdd(Today(), 'd', -1*DayOfWeek(Today())-6))

Hope that helps!

Laura

 

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
K
Rank
Userlevel 4
Badge

Hi @lauraj46, thank you very much! That worked!! Have a great day! 😊

Kaavya
K
Rank
Userlevel 4
Badge

Hey @lauraj46 , it worked on the GI but not on the report writer/ report designer. We get the below error, is there a different formula that we can use on the report writer? thank you!

 

 

Kaavya

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