To find overage order amount for last year USE DATEDIFF or DATEPART or DATEADD

[Ashley]

I need to find out the avg amount of Ordertotal for last 1 year and
this year


if location is USA or Canada

This query gives me error for present year and how do I do this for
last year



avg((IIf((([Location]="USA") Or ([Location]="CANADA")) And ((
(datepart( 'y', ([OrderDate]))) = (datepart('y', date() ) )),
Ordertotal ,0))) AS [Average Order Amount This year],



Will this work for last year probably not


avg((IIf((([Location]="USA") Or ([Location]="CANADA")) And ((
(datepart( 'y', ([OrderDate]))) = (datepart('y', dateadd('y', -1,
date()) ) )), Ordertotal ,0))) AS [Average Order Amount This year],

 

[John Spencer]

First, to get the Year using datapart, you should be using "yyyy" not "y".
I would simply use the Year funciton. Also, I think you want to use Null
not zero in the calculation. If your if returns zero, then the Avg will use
the zero in the calculation, if you use null then the value will not be used
in the calculation of the average.

Avg(IIF(Location="USA" or Location="Canada" And Year(OrderDate) =
Year(Date()), OrderTotal, Null) as AvgOrderThisYear

Prior Year would be

Avg(IIF(Location="USA" or Location="Canada" And Year(OrderDate) =
Year(Date()-1), OrderTotal, Null) as AvgOrderPriorYear