Solved

How to use both COUNT & SUM aggregate functions on 1 line in GI

  • 10 March 2023
  • 2 replies
  • 619 views

Userlevel 4
Badge

I’m working on a report to calculate the physical inventory count accuracy. 

I’m able to calculate the total number of lines counted, and how many of those lines had variances, but I would like to display the accuracy as a % value. This requires me to do some calculations on a single line, and I can’t quite figure out how to achieve that. 

I have the following so far:

The corresponding SQL for the required outcome is:

SELECT
`INPIDetail`.`PIID`,
`INPIHeader`.`CountDate`,
COUNT(*) AS `TotalLines`,
SUM(CASE WHEN `INPIDetail`.`VarQty` = 0 THEN 0 ELSE 1 END) AS `Variances`,
ROUND(((COUNT(*) - SUM(CASE WHEN `INPIDetail`.`VarQty` = 0 THEN 0 ELSE 1 END)) / COUNT(*)) * 100, 2) AS `Accuracy`
FROM `INPIDetail` `INPIDetail`
INNER JOIN `INPIHeader` `INPIHeader`
ON `INPIHeader`.`PIID` = `INPIDetail`.`PIID`
AND `INPIHeader`.`CompanyID` = `INPIDetail`.`CompanyID`
WHERE `INPIDetail`.`CompanyID` = X
GROUP BY `INPIDetail`.`CompanyID`, `INPIDetail`.`PIID`, `INPIHeader`.`CountDate`
;

As seen above, I have the SUM(CASE WHEN `INPIDetail`.`VarQty` = 0 THEN 0 ELSE 1 END) part of the query figured out, but the part that I’m struggling with is the ability to subtract this amount from the count of all records. 

Any ideas on how I can achieve the desired outcome?

PS. I know I can very easily do this with a SQL view, and then create my own DAC and then simply just build a GI off of that DAC, but I’m wondering if there’s a way to achieve this without doing all of that. 

Thanks in advance 😊

icon

Best answer by lauraj46 14 March 2023, 14:46

View original

2 replies

Userlevel 4
Badge

Awesome! Thanks @lauraj46 . I ended up doing the view, but will bookmark this for the next time 👌🏻

Userlevel 7
Badge +7

Hi @chris49 ,

You should be able to use a formula similar to this one:

SUM(iif([INPIDetail.VarQty] = 0, 0.0, 1.0)/count([INPIDetail.PIID])

You need the decimal on the iif, otherwise the result of the division will be an integer instead of a decimal.

When you use the aggregate function in the formula, you can leave the aggregate function column blank.  

Hope this helps!

Laura

 

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