Solved

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

  • 10 March 2023
  • 2 replies
  • 588 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 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

 

Userlevel 4
Badge

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

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