Skip to main content

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 😊

Hi @chris49 ,

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

SUM(iif(fINPIDetail.VarQty] = 0, 0.0, 1.0)/count(nINPIDetail.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

 


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


Reply