I am trying to do the following calculation
On the screenshots below for 1. it should have Cover Qty LM = 1.363 and Cover Qty LY = 2.664 for the top line result.
I cant seem to get it to work. What am I missing?
Best answer by ryonxan
View original
+18
Hello
It’s hard to answer without seeing the complete Results with labels and the grouping tab. Specifically, How are you Calculating ARTran.Qty?
ARTran.Qty is the quantity sold on one line of one invoice, unless you are aggregating something. We can’t tell if you’re aggregating anything, or how it might be aggregated, with the limited screen shots shown.
It seems like Qty on Hand = Cover Quantity because in your example, ARTran.Qty = 1.
Can we please see Grouping, Joins, etc. or just post the XML? Many people like to download your XML before answering.
Laura
Sorry for that, here is the XML
Hello
I took your inquiry and imported it into my local instance and began working on your formula. My Data was too old to produce any values for the “SoldLast30” formula but I was able to get the desired result for the “Cover Qty LY” Formula with my data in my instance. Pasted below is the Formula that I used. The first part is a simple if statement used to remove any divide by zero errors, and within the if statements True Part is the formula that essentially does (Qty on Hand / Sold365Avg).
Formula in Data Field of Results Grid:
=IIf( Sum((IIf(DateDiff( 'd', [ARTran.TranDate], Today() )<=365,[ARTran.Qty] , 0))/12)<>0, Sum([INSiteStatus.QtyOnHand]) / Sum((IIf(DateDiff( 'd', [ARTran.TranDate], Today() )<=365,[ARTran.Qty] , 0))/12), 0 )
Hopefully that works for you as well and you can reverse engineer it for the “SoldLast30” version of the calculation. Let us know your results.
Hey Matt, thanks for that. I am still getting some large numbers rather than what I am expecting.
For example if the last 365avg is 29.08 and the Qty On Hand is 86, I am getting Cover Qty LY = 1088.18
In the schema field it has ARTran.Qty. Is there something I am doing wrong?
Hey Matt,
Thanks for jumping in on this, with your help I got to the bottom of the calculation
For Cover based on LM:
=[INSiteStatus.QtyOnHand]/Sum(IIf(DateDiff( 'd', [ARTran.TranDate], Today() )<=30,[ARTran.Qty] , 0))
Cover based on 12M Average:
=[INSiteStatus.QtyOnHand]/Sum(IIf(DateDiff( 'd', [ARTran.TranDate], Today() )<=365,[ARTran.Qty] , 0)/12)
For clarity the Fields are:
SoldLast30 days
Sold365Avg - Sold over last 365 days divided by 12
Cover based on LM is the Qty on Hand divided by SoldLast30
Cover based on 12M Average is the Qty on Hand divided by Sold365Avg
I have attached a copy of the updated XML if anyone else is interested.
Thank you for sharing your solution with the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
