% Change Formula 2

[Kreiss]

I have a Crystal report that has two columns.......

Year Total

So in my design view in crystal I have the detail section suppressed and a group footer changed by "year". I would like to add a percent change from the previous year to the to the current year as a column. So I know I want to do something along the lines of having a running total using a formula that says..... year = year - 1. That way I can get last years total, then I can do the rest of the calculations to get the answer. However, I'm not having any luck even getting anything to display in the running total w/ the formula.


Example.....

Year Total %change
2002 500 .25
2001 400 .33
2000 300
etc, etc, etc,

I would appreciate any advice.

Thanks in advance,

Kacy

 

[synapsevampire]

Here's a simple option:

Create a Crosstab and place the year in the rows (select the year field afterwards and select Group Options to change it to For Each Year and sortation to descending), and place the amount in the columns.

-k

 

[synapsevampire]

Sorry, Amount in the Summarized Field.

-k

 

[Ngolem]

I would order your report descending by year, rather than Ascending

Year Total %change
2000 300
2001 400 .33
2002 500 .25

then the formula is as follows

//@PercentChange

WhilePrintingRecords;
StringVar result := "";

if not onFirstRecord then
(
if previous({Table.total}) = 0 then
result := "Inf" // infinately big change
else
result := totext(({Table.total} - previous({Table.total}))/previous({Table.total}) * 100,2) + "%";
);

result;

BTW...your result only showed the fractional change ... not the % change...I gave it 2 decimals in the above formula...you can adjust it accordingly.

Jim Broadbent

 

[Kreiss]

Ngolem,
This looks pretty close to what I need, but I really need the years in descending order. What do I need to change to reflect this? Thanks!

 

[Ngolem]

ok...it isn't too much more...we just use NEXT instead of Previous and test for the Last record instead of the first

//@PercentChange

WhilePrintingRecords;
StringVar result := "";

if not onLastRecord then
(
if Next({Table.total}) = 0 then
result := "Inf" // infinately big change
else
result := totext(({Table.total} - Next({Table.total}))/Next({Table.total}) * 100,2) + "%";
);

result;


Jim Broadbent

 

[Kreiss]

Thanks Jim,
You've been a big help...I've spent the last two days trying to figure this out. Let me throw another curve ball at you.

We are going to be writting a procedure starting on 12/31/2003 and on to populate the data. However, statically we have the data all the way back to 1985. What I have done is put the 1985 to 2002 data in the report footer and manually created the % change column. I created dummy data for 2003, 2004 and 2005 to test the formula you gave me. It works great, but it show's nothing for 2003, because that is the "first" record. Is there anyway I can have it for 2003...(the earliest date in my table) to do the calculations so I can get the percent change from 2002? (2002 is the first record in the footer.

This is what it currently looks like...
(this is the detail section)

Year Total % Change
2005 3,861,152 4.34
2004 3,700,637 -56.67
2003 8,541,234 ****** this is blank
****start of <u>Footer</u>
2002 8,526,322 .11
2001 8,516,798 .75
etc....etc...etc.

I need to figure out how to calculate 2003 % Change.

Thanks Again!!
Kacy

 

[Kreiss]

Think I have it......Let me know if you see anything wrong with this..... or better way.

if onLastRecord then

totext((8541234 - 8526322) / 8526322 *100,2)

else

totext(({Licensed_Total;1.Total} - next({Licensed_Total;1.Total}))/next({Licensed_Total;1.Total}) * 100,2)

Thanks!

 

[Ngolem]

That will work of course...you have hard coded this so it will require maintenance in the future if you change the report footer.

What I do if I do hard code something like that is I create a dummy formula called @ReadME and place it in the report footer suppressed and the background of the formula is colored red as a flag to draw the attention of someone doing report maintenance later on

When you suppress it use a conditional suppress formula like

if 1 = 1 then true

obviously this always evaluates to true but Crystal does not know this til it executes so it leaves the red background alone in the design stage

I often use this approach to document changes to a report ....it is amazing how you alone will forget the thought process you went through to write this report in the first place 2 months from now

the formula is just a series of comment statements

//@ReadME

//October 8, 2003
//Developer: John Doe
//Comments:
// NOTE: the last calculation of the %change (2003)is
//hard coded based on the total that is hard coded in the
//report footer for year 2002. If moving total data into
//the report footer then @PercentChange needs to be
//modified.

This formula can be as big as you like and is not compiled at run time so there is no effect on performance.

I note that you didn't include the case where there is a ZERO total...I think it should be there for completeness even though at present you don't think it can happen....2 years from now it may happen.


Jim Broadbent

 

[Kreiss]

Just wanted to say thank you Jim, you've been a big help!

 

[sewilj]

Jim

Great tip for the @README formula!!!!!

I think I shall be using that in the future....



Lewis
United Kingdom