Solved

Rolling moving annual total

Posted on 2009-03-29
12
940 Views
Last Modified: 2012-05-06
Hi Experts,

I'm trying to write a query that gives me a moving annual total of sales based on a table with the following data structure:

ItemCode      TheDate      NetDollarsInvoiced
KT100R      01-Jul-07      12598
KT100R      01-Aug-07      8306
KT100R      01-Sep-07      4765
KT100R      01-Oct-07      4746
KT100R      01-Nov-07      20131
KT100R      01-Dec-07      2942
KT100R      01-Jan-08      1409
KT100R      01-Feb-08      735
KT100R      01-Mar-08      1854
KT100R      01-Apr-08      1591
KT100R      01-May-08      4653
KT100R      01-Jun-08      2541
KT100R      01-Jul-08      3486
KT100R      01-Aug-08      1454
KT100R      01-Sep-08      733
KT100R      01-Oct-08      682
KT100R      01-Nov-08      902
KT100R      01-Dec-08      1282
KT100R      01-Jan-09      1638
KT100R      01-Feb-09      5057

I want the query result to be as follows:
ItemCode      TheDate      NetDollarsInvoiced Last 12 months
KT100R      01-Jun-08      66271
KT100R      01-Jul-08      57159
KT100R      01-Aug-08      50307
KT100R      01-Sep-08      46275
KT100R      01-Oct-08      42211
KT100R      01-Nov-08      22982
KT100R      01-Dec-08      21322
KT100R      01-Jan-09      21551
KT100R      01-Feb-09      25873

The issue is that there are 100's of item codes so a dlookup is out of the question (takes too long and inevitably crashes.

Is there another way to do this?

Suggestions gratefully received
0
Comment
Question by:DanielGra
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
12 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 125 total points
ID: 24015302
Try something like this

SELECT t1.ItemCode, t1.TheDate,
      (SELECT Sum(t2.NetDollarsInvoiced)
      FROM tblSales t2
      WHERE t2.TheDate Between DateAdd("m", -11, t1.TheDate) And t1.TheDate) AS Last12Mo
FROM tblSales t1
ORDER BY t1.ItemCode, t1.TheDate


Replace instances of tblSales with your actual table name.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24015312
OK the month is March 09,  the last 12 months to my thinking go from March 08 to February 09.  Whether or not you include March 09 pales in comparison to: "What happened to April and May of 08?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24015317
Anyway:

SELECT * FROM tblTotSales WHERE TheDate BETWEEN DateAdd("m",-12, Date()) AND Date()
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:DanielGra
ID: 24015419
Thanks Guys,

I think MathewPatricks solution works (after running a quick check) - Although I haven't seen this approach before - looks like the quesry creates a sub table t2 that pulls out the correct months that are then sumed - correct?

I'm not sure that GRayL's solution will work because it will not pull a rolling month total on every line. It will just pull the last 12 months from today's date (ie it wont tell me the 12 months up to Dec '08)
0
 

Author Comment

by:DanielGra
ID: 24015536
Hi Mathews patrick:

I ran your code as follows:

SELECT t1.ItemCode, t1.TheDate, (SELECT Sum(t2.revenue)
      FROM tblLifeCycle t2
      WHERE t2.TheDate Between DateAdd("m", -11, t1.TheDate) And t1.TheDate) AS Last12Mo
FROM tblLifeCycle AS t1
WHERE (((t1.ItemCode)="kt100r"))
ORDER BY t1.ItemCode, t1.TheDate;

And got the following results:

ItemCode      TheDate      Last12Mo
KT100R      1/07/2007      17383495
KT100R      1/08/2007      34983568
KT100R      1/09/2007      54913400
KT100R      1/10/2007      81593893
KT100R      1/11/2007      106965587
KT100R      1/12/2007      122771431
KT100R      1/01/2008      136334994
KT100R      1/02/2008      148210569
KT100R      1/03/2008      162925525
KT100R      1/04/2008      181239768
KT100R      1/05/2008      199465450
KT100R      1/06/2008      213609107
KT100R      1/07/2008      215489688
KT100R      1/08/2008      215871983
KT100R      1/09/2008      216424031
KT100R      1/10/2008      218917899
KT100R      1/11/2008      218472023
KT100R      1/12/2008      223527081
KT100R      1/01/2009      228470753
KT100R      1/02/2009      230579162

Which look to me like the sum of all item codes for the Rolling last 12 months - can it be tweaked to only sum for each item code?

Thanks
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24016108
try this
SELECT t1.ItemCode, 
       t1.TheDate, 
       (SELECT Sum(t2.revenue)
          FROM tblLifeCycle t2
         WHERE t2.TheDate Between DateAdd("m", -11, t1.TheDate) And t1.TheDate
           AND t2.ItemCode = t1.ItemCode) AS Last12Mo
  FROM tblLifeCycle AS t1
 WHERE (((t1.ItemCode)="kt100r"))
 ORDER BY t1.ItemCode, t1.TheDate;

Open in new window

0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 125 total points
ID: 24016109
If you don't want the ItemCode filter in the main query, you can remove that.
SELECT t1.ItemCode, 
       t1.TheDate, 
       (SELECT Sum(t2.revenue)
          FROM tblLifeCycle t2
         WHERE t2.TheDate Between DateAdd("m", -11, t1.TheDate) And t1.TheDate
           AND t2.ItemCode = t1.ItemCode) AS Last12Mo
  FROM tblLifeCycle AS t1
 ORDER BY t1.ItemCode, t1.TheDate;

Open in new window

0
 

Author Comment

by:DanielGra
ID: 24016186
Hey that worked - thanks everyone for your help - I learned something new with solving this  problem
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24017831
DanielGra,

You are correct in the interpretation--the subquery calculates the rolling sum.  Of course, I forgot to also
put the ItemCode criterion in the subquery, and that is why the sums were coming up wrong.  My thanks
to Sharath_123 for making that correction :)

Using subqueries is one of the tricks I learned hanging out here on Experts Exchange.

Regards,

Patrick
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24019601
You are welcome MP. even i learned that method at EE only. :)
0
 

Expert Comment

by:yoh268
ID: 25847892
hi guys... can you help me? what if i need to have a moving annual total for two or more data entry?

here's an example heading:

product | total cases | price

i need to get the running sum for 'price' and 'total case'

thanx
0
 

Expert Comment

by:yoh268
ID: 25847904
oop there should be a date heading

date | product | total cases | price
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question