By using this website, you agree to our Terms of Use (click here)
We have a non-stock item for which we would like a trend of the weighted average weekly purchase price over the last 6 month period (can extend to 1 year if necessary).
The source of the data is (SUM(POLine.ReceivedCost) / SUM(POLine.ReceivedQty)) aggregated by Week (ending Sunday).
I have been able to do bits of this, but not the whole solution. For example, I can convert the OrderDate to a Sunday date in a GI, but I cannot group by that as it is a formula and GI only allows group by data fields (massive limitation). I can return the raw data (1 row per PO line) from the GI and use a Chart widget with an Average over the calculated price (POLine.ReceivedCost / POLine.ReceivedQty) but that would be a statistical average and not a weighted average. Because we have different vendors supplying the same item with price variances per vendor, weighted average is critical. Â
Any suggestions?
Is this something that needs a BI solution?
A BI solution like Power BI would definitely be better at doing this kind of calculation.
But it seems to me like the main thing you are missing is the ability for each row calculation to be aware of the grand total:Â SUM(POLine.ReceivedQty). Because of that, I can't think of a way to do this in a Generic Inquiry.
What you might try though is Report Designer with a Subreport that calculates the Grand Total and passes it back to the main report in a variable. Then the variable could be used in the calculation to weight the averages. Something like this:
(SUM(POLine.ReceivedCost) / SUM(POLine.ReceivedQty)) * (SUM(POLine.ReceivedQty) / $Subreport_GrandTotal_ReceivedQty)
Thanks Tim. The end user was happy to download the GI data to excel and do the average calc with a formula.
Â
Good old Excel, the world's most popular Business Intelligence tool 🙂 (I personally love Excel for this reason).
You might try connecting the Generic Inquiry to Excel via OData at some point to make their life a little easier.
Online Members
