Year over Year Calculation Error

[cmnapper]

I have 3 separate tables. The first contains the list of Merchants I need to pull. The second contains sales data for 2011 the sales for 2010. I have joined the tables on the client name. When I add data, the query runs fine. When I add this calculation =(2011 sales-2010 sales)/2010 sales, I get this error Division by zero, my only option is to click on OK. What am I not doing correctly?

 

[Joe4]

It sounds like you either have records in your 2010 sales table with a sales amount of zero, or you have clients in your 2011 table that do not exist in your 2010 (you would only see these if you are using a LEFT or RIGHT JOIN instead of an INNER JOIN).

So, in either case you need to account for the possibility of your 2010 sales amount of being zero or null.

If zero, they try:
MyCalc: ([2011 sales]-[2010 sales])/IIF([2010 sales]=0,1,[2010 sales])

If null, they try:
MyCalc: ([2011 sales]-[2010 sales])/NZ([2010 sales],1)