Solved

Cover quantity of stock calculation

  • 13 September 2023
  • 6 replies
  • 92 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

 

 

icon

Best answer by ryonxan 14 September 2023, 07:04

View original

6 replies

Badge +18

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

Sorry for that, here is the XML

Userlevel 3
Badge

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.

 

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.

 

Userlevel 7
Badge

Thank you for sharing your solution with the community @ryonxan

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved