We need to flag inventory items that fail to hit our expected monthly sales growth targets, by comparing current‑month sales against the same month’s sales from the previous year.
For example: Item A sold 100 units in May last year, with a predefined 2% sales growth target. If it sells fewer than 102 units in May this year, this item should be listed out for review.
Is this logic achievable within Acumatica Generic Inquiry? If so, could you share the approach or required data sources to implement this YoY sales growth comparison?
Best answer by craig2
Hi @ray20 ,
Just throwing some sketches out there to try, but this feels like a job for Subqueries! I would pull together SOOrder and SOLine, group by SOLine.InventoryID, and set your request date to between @MonthStart-12 (start of current month, last year) and @MonthEnd-12 (end of current month, last year):
From there just add your InventoryID and OrderQty/Shipped Qty to the Results with a Sum aggregation.
Then, I would repeat this process with a second Subquery, still grouping by InventoryID but now just using @MonthStart and @MonthEnd.
Finally, in your master GI, pull in the InventoryItem table and your two subqueries, Inner joining at the InventoryID.
From there you should just be able to add InventoryItem.InventoryID, your YoY Sum value from the first Subquery, and your CM Sum value from your second. I believe you should be able to add a Subquery1.YoYSale * 1.02 field, if that’s what your target is. From there you should also be able to do a fourth comparison column, subtracting one from the other, to see where things are falling off.
Just some caveats, I’ve run into issues with using Groups in Subqueries. I think this scenario is straightforward enough that it should work, or at least give you a starting point.
Just throwing some sketches out there to try, but this feels like a job for Subqueries! I would pull together SOOrder and SOLine, group by SOLine.InventoryID, and set your request date to between @MonthStart-12 (start of current month, last year) and @MonthEnd-12 (end of current month, last year):
From there just add your InventoryID and OrderQty/Shipped Qty to the Results with a Sum aggregation.
Then, I would repeat this process with a second Subquery, still grouping by InventoryID but now just using @MonthStart and @MonthEnd.
Finally, in your master GI, pull in the InventoryItem table and your two subqueries, Inner joining at the InventoryID.
From there you should just be able to add InventoryItem.InventoryID, your YoY Sum value from the first Subquery, and your CM Sum value from your second. I believe you should be able to add a Subquery1.YoYSale * 1.02 field, if that’s what your target is. From there you should also be able to do a fourth comparison column, subtracting one from the other, to see where things are falling off.
Just some caveats, I’ve run into issues with using Groups in Subqueries. I think this scenario is straightforward enough that it should work, or at least give you a starting point.
@craig2 Thank you for your great idea, the need is done. My steps are below”
Step 1: Create Sub GI 1 (Last Month Sales Quantity per Item) Query the ARTran table, filter transactions for the previous calendar month, group by Inventory Item ID, and calculate total sold quantity as ItemTotalLastMonth.
Step 2: Create Sub GI 2 (Same‑Month Sales Quantity Last Year per Item) Query the ARTran table again, filter transactions for the same month of last year, group by Inventory Item ID, and calculate total sold quantity as ItemTotalLastYearSameMonth.
Step 3: Master GI for Growth Ratio Calculation Join the two sub‑GIs on Inventory Item ID, then compute the sales growth ratio: Ratio = ItemTotalLastMonth / ItemTotalLastYearSameMonth
Business Rule & Dashboard Filter We set a target sales growth of 2% year‑over‑year. Items with a ratio less than 1.02 fail to meet the growth target. I embedded this master GI into the Dashboard and applied a filter to only show items where Ratio < 1.02 for review.
This solution is real‑time, dynamic, and requires no customization or scheduled data updates.
