?
Solved

Running Total for Trailing Twelve Months

Posted on 2006-06-13
9
Medium Priority
?
776 Views
Last Modified: 2007-10-19
How do I set up a running total field so that it resets itself after every twelve months, assuming this is the right way to accomplish what I am trying to do. for example, for each month of investments, sum up that month and the last twelve months of investments on that month's row.

I have several years worth of financial trade data and so far I have been able to group the trades by month.  Now I want to show a field next to it that totals the amount of trades made for the last twelve months respectively.  May be running total field is not the way to do this?

Example below of what I am trying to accomplish:

Month      Amount       Running Total Last Twelve Months
Mar 05       20            
Apr 05        10
May 05       10
Jun 05        10
Jul 05         20
Aug 05       10
Sep 05       30
Oct 05        40
Nov 05       10
Dec 05       20
Jan 06        10
Feb 06        50
Mar 06       10            250 (this is the sum of Mar'05 - Mar'06)
Apr 06        30           260  (this is the sum of Apr'05 - Apr'06)
May 06       40           290  (this is the sum of May'05 - May'06)
Jun 06        50           330  (this is the sum of Jun'05 - Jun'06)
etc....

Thanks for your help.  I hope my question is clear.
0
Comment
Question by:IO_Dork
[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
  • 4
  • 4
9 Comments
 
LVL 9

Expert Comment

by:Outin
ID: 16900653
Something like this could do the trick:

  //@TrailingSum
  numbervar array PreviousMonths[12];
  numbervar i;
  for i := 1 to 11 do
  (
   PreviousMonths[i] := PreviousMonths[i+1];
  );
  PreviousMonths[12] := {LEDGER_ITEM_CU_QRY\.INV_DOM_AMOUNT};
  sum(PreviousMonths);

--
Outin
0
 

Author Comment

by:IO_Dork
ID: 17081872
How do I incorporate this formula into my running total formula? or do I just scrape the running formula and create a new Formula Field based on your formula above?
0
 
LVL 9

Expert Comment

by:Outin
ID: 17081893
This would be a new formula indeed...

--
Outin
0
Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

 

Author Comment

by:IO_Dork
ID: 17081920
ok, thanks.  I'll give it a try.
0
 

Author Comment

by:IO_Dork
ID: 17105601
I am not able to get this formula to work.  Also, I don't see anywhere in the formula that looks at the date field and the amount field so that it can sum up the investment amounts for the trailing twelve month period.  The two fields in question are {trade.settlement_date} and {trade.investment_amount}.

I guess I just don't understand how this formula is supposed to be incorporated in to the report other than adding it as a new formula field, but when I try to do that crystal reports gives me an error when trying to save the formula and it tells me that the field name {LEDGER_ITEM_CU_QRY\.INV_DOM_AMOUNT} is not known.
0
 
LVL 9

Accepted Solution

by:
Outin earned 2000 total points
ID: 17108956
Sorry, my bad.
"{LEDGER_ITEM_CU_QRY\.INV_DOM_AMOUNT}" is a field I used when testing this solution.
You should replace it with the field you want to calculate the trailing sum for.

Besides that, I assumed you had 1 record for every month, yet now I notice you have more records for each month.
So proceed like this:
- Group your data by month
- Create the formula mentioned previously, place it in the group footer.
   Replace the '{LEDGER_ITEM_CU_QRY\.INV_DOM_AMOUNT}'-part with

    Sum({trade.investment_amount}, {trade.settlement_date}, "monthly")

--
Outin
0
 

Author Comment

by:IO_Dork
ID: 17110213
FYI - I acually have a record for every trade done, so that means a few thousand trades a month.  So what I did was tell cyrstal reports to group all trades by month and had it sum up the dollar amounts of all trades per month. that is what I am currently working with now and that is the state of the report that I am now trying to add an additional column to sum the trailing twelve months in dollar amounts invested.
0
 
LVL 9

Expert Comment

by:Outin
ID: 17239502
Then the solution I proposed in my previous post should work for you....

--
Outin
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

765 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