By using this website, you agree to our Terms of Use (click here)
I am working on a GI that shows returns for the year by day and or by month, but I was wondering if there was a way to accomplish by showing returns by each week to get a weekly percentage.Â
Thanks,
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.
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 😉
Â
Nice Hazen!