Sliding Comparative analysis

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I was wondering what sort of query I would build if i had the following
I have a range of products and i want to be able to measure their sales in
Volume and Gross Profit, and compare last years results for the same Product
range for the same week of the year and then work out the variance between
both of them.

Any Ideas would be a great help!

Cheers
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Kinda hard w/o your table designs. Perhaps this:

SELECT
ProductID,
Year(SalesDate) As SalesYear,
Count(*) As Volume,
Sum(Sales) As TotalSales
FROM Sales
WHERE SalesDate Between #8/23/2004# And #8/27/2004#
OR SalesDate Between #8/25/2003# And #8/29/2003#

The WHERE clause designates the year & the week (last in August). You
may wish to use the VBA functions Year() and/or DatePart(). E.g.:

WHERE Year(SalesDate) In (2003, 2004)
AND DatePart("ww", SalesDate) = 32

32 = the week of the year.

See the VBA Help articles on Year Function and DatePart Function.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQSpWxIechKqOuFEgEQJyFgCfbK3xyqgULHfpUQqSDCQ0dfu/ZDAAnAmt
WpMFkk4WzJ8/Z0Mea5/1RW/+
=q4U8
-----END PGP SIGNATURE-----
 
Back
Top