We help IT Professionals succeed at work.

# Running Total for Trailing Twelve Months

on
799 Views
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.
Comment
Watch Question

## View Solution Only

Commented:
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

Commented:
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?

Commented:
This would be a new formula indeed...

--
Outin

Commented:
ok, thanks.  I'll give it a try.

Commented:
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.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)

Commented:
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.

Commented:
Then the solution I proposed in my previous post should work for you....

--
Outin
Unlock the solution to this question.

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.