Anyone know how to create a GI that displays the average customer order count over a rolling 12 month period? I’d also need to be able to exclude specific order type (mainly returns).
I’m just not sure how to get an order count using formulas. And I would need to include any open (non-completed) orders in the count as well.
Best answer by brendan91
I created a GI that summarizes the number of orders for each month (GI xml attached). I would export via Odata to excel or Power BI (or just do a regular ol’ sheet export) and then do a moving average on the summaries.
https://www.ablebits.com/office-addins-blog/moving-average-excel/
+1I think you will need to write a SQL script and publish this as a SQL view as you need summaries for different time periods within the same table.
The best you could probably do through a GI is calculate the number of orders within a single 12-month period. Hopefully, in the future, Acumatica will let us combine GIs.
This is a good idea though, and I will copy the idea and add it to my list of things to do. I will share the script if/when I get to it.
How would you suggest calculating the # of orders within a single 12-month period in a GI?
+1I created a GI that summarizes the number of orders for each month (GI xml attached). I would export via Odata to excel or Power BI (or just do a regular ol’ sheet export) and then do a moving average on the summaries.
https://www.ablebits.com/office-addins-blog/moving-average-excel/
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
