Skip to main content
Solved

Cover quantity of stock calculation

  • September 13, 2023
  • 6 replies
  • 119 views

I am trying to do the following calculation

  1. Qty on Hand / SoldLast30
  2. Qty on Hand / Sold365Avg

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?

Results
GI

 

 

Best answer by ryonxan

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.

 

View original
Did this topic help you find an answer to your question?

6 replies

Laura02
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3132 replies
  • September 13, 2023

Hello @ryonxan ,

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


  • Author
  • Freshman II
  • 7 replies
  • September 13, 2023

Sorry for that, here is the XML


mbarrett
Jr Varsity III
Forum|alt.badge.img+1
  • Jr Varsity III
  • 10 replies
  • September 13, 2023

Hello @ryonxan,

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.

 


  • Author
  • Freshman II
  • 7 replies
  • September 14, 2023

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?

 


  • Author
  • Freshman II
  • 7 replies
  • Answer
  • September 14, 2023

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.

 


Chris Hackett
Community Manager
Forum|alt.badge.img
  • Acumatica Community Manager
  • 2789 replies
  • September 14, 2023

Thank you for sharing your solution with the community @ryonxan


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings