[Solved] Using Weekly Instead of Daily, Monthly, or Yearly
Perhaps you need to start thinking about Power BI for all your advanced reporting needs. I did, and I've never looked back. GI can only do so much.
I am not aware of any function in GI to determine current week.
In Power BI you would solve it via a date table. I guess you could do something similar in Acumatica if you can create a new table.
I also like the idea of Power BI for this.
The only thing I can think of within Acumatica would be to use the DateInfo table, specifically the DateInfo.WeekEnding field.
Interested in joining a Local Acumatica User Group? Click here for more info
You could create a "Week Start Date" column using:
= DateAdd( [SOLine.OrderDate], 'd',1-IIf( DayOfWeek( [SOLine.OrderDate]) = 1 , 7, DayOfWeek( [SOLine.OrderDate])-1 ))
Then turn your data into a pivot table. Make sure that your pivot table field for the week start date is formatted to: yyyy/MM/dd - in this way you can sort correctly by date.
This is working off our line item data, but you could replace line date with whatever date you were working with. I did steal this from our Kensium developers, just so I'm not taking all the credit 😉