query to find avg of ordertotal from last year and this year

query to find avg of ordertotal from last year and this year

Post by Ashle » Sat, 30 Sep 2006 03:56:41


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],