Calculate Percent difference between two columns

[didiomm]

Hi All:



I have a query that I'm working on in Access. As the title says, I am trying to get the percent difference between column A and column B.

This is what I tried:

WHERE IIf(([columnA]-[columnB])>1,([columnA]-[columnB]),([columnA]-[columnB])*-1)<>False

I *think* this is returning all dollar amount difference between these columns that is above ~$1. I say "~$1" because one of the records has (column A) 911.13 vs (column B) 910.14. That is $1 when rounded up. But what I need is 1% difference between column A and column B (regardless of which column has the higher value.)

Any ideas?

Thanks!

[John_G]

You are just calculating differences, not percent differences. The formula for percent difference is (a-b)/b OR (a-b)/a, and they are NOT the same. Those give the differences as decimals; if you want to express the difference as a percent, multiply by 100 : (a-b)/b*100. Since you don't care about positive or negative, use the abs function (absolute value):

abs(a-b)/b*100 OR abs(a-b)/a*100

If the differences between a and b can be large, then the choice of a or b in the denominator (in red) is critical, but I can't help you out there.

Just a question, though - is "percent difference" really what you are looking for (as I showed you above), or is it something else?

[ssanfu]

I don't understand why are you only showing a where clause?

How about showing some data and expected results?

Example

ColA	ColB	PctDiff
100	50	0.3333
75	35	0.3636

EDIT: Got hit by the copy and paste bug....
After seeing the following post, here is my modified SQL:

SELECT SystemA.CustName, 
qryCombin2Reports.PaidAmount AS ColumnA, 
SystemA.PaidTotal AS ColumnB, 
SystemA.PaidDate, 
IIf(Nz(([ColumnA]+[ColumnB]),0)=0,100,Abs(Round(([ColumnA]-[ColumnB])/([ColumnA]+[ColumnB])*100,2))) AS PctDiff
FROM SystemA INNER JOIN qryCombin2Reports ON SystemA.CustName = qryCombin2Reports.CustName
WHERE (((IIf(Nz(([ColumnA]+[ColumnB]),0)=0,100,Abs(Round(([ColumnA]-[ColumnB])/([ColumnA]+[ColumnB])*100,2))))>0.1));

I realize John_G provided the answer, but I didn't want to leave code that had errors.....

[didiomm]

John_G and Sanfu:

Thank you both. The code that is needed is to only display the columns with data of greater than 1% between the two amounts reported. This would indicate a discrepancy between 2 systems in the way they are calculating what has been paid. If one system says someone paid $100.00 but the other system is reporting $76.00, then that person would show up in the list of having a greater than 1% difference between the two reporting systems. If one system says someone paid $9.11 and the other system says that the same person paid $9.12, then that person would not show up on the list. Either system could report null values.

Didn't include the whole statement as what was originally posted is the only part of the statement that was problematic. But, for clarity's sake:

SELECT SystemA.CustName, 
qryCombin2Reports.PaidAmount AS ColumnA, 
SystemA.PaidTotal AS ColumnB, 
SystemA.PaidDate
FROM SystemA INNER JOIN qryCombin2Reports ON SystemA.CustName = qryCombin2Reports.CustName
WHERE IIf(([columnA]-[columnB])>1,([columnA]-[columnB]),([columnA]-[columnB])*-1)<>False

Also, given the data below, only Lee (10.05% difference)and Gotno (9.46% difference) would show up in the output for having a payment discrepancy of greater than 1%.

CustName	ColumnA	ColumnB	PaidDate
John Doe	12.75	12.76	09/15/2013
Lee Doe*	88.57	72.38	07/29/2014
Gotno Doe*	750.00	620.25	06/11/2016

[John_G]

Your basic WHERE clause would then be something like this:

WHERE Abs(([columnA]-[columnB]))/[columnA]*100.0 >1

You need the Abs to ensure the percent difference will always be positive.

I did a little testing, and it turns out that if either [ColumnA] or [ColumnB] is Null, the whole expression is Null, so an easy fix for that is the Nz:

WHERE Nz(Abs([columnA]-[columnB]))/[columnA]*100.0, 0) > 1

If [ColumnA] can ever be 0 instead of Null, this will fail with a run-time error of division by zero.

[didiomm]

Thanks, John_G. I played around with the WHERE clause you suggested. I modified the clause to this:

WHERE Nz(Abs([columnA]-[columnB]))/[columnA]*100) > .01

This yielded 514 correct results

WHERE Nz(Abs([columnA]-[columnB]))/[columnA]*.10) > .01

This yielded 36 correct results

WHERE Nz(Abs([columnA]-[columnB]))/[columnA]*100) > 1

This yielded 3 correct results



The version of Access I'm currently using is 2010. I tagged this thread as Access 2010, but realize my profile says v 2013-I apologize. I mention this because Access threw out an error:

Syntax error (comma) in query expression 'WHERE Nz(Abs([columnA]-[columnB]))/[columnA]*100.0, 0) > 1'

It didn't like the comma for any of the WHERE clauses. I'm guessing that the code yielding 514 results is the best fit...

[John_G]

Syntax error (comma) in query expression WHERE Nz(Abs([columnA]-[columnB]))/[columnA]*100.0, 0) > 1

You have too many closing brackets (the extra one is in red). Try this:

WHERE Nz(Abs([columnA]-[columnB])/[columnA]*100.0, 0) > 1

I'm guessing that the code yielding 514 results is the best fit...

That depends on how big a difference you want to allow without flagging it as an error. The expression that gives you 514 results is listing differences greater than one one-hundreth of 1 percent, or 1 cent in $100 as errors. Only 3 are greater than 1% difference.

[didiomm]

Thank you, John_G! The elimination of the extra bracket worked, and I understand the explanation of the expressions' differences.

Thanks again!