Calulate Percentage 1

[wld1957]

Using CRXI. I have a good working knowledge of Crystal. I can get this correct in Excel but have trouble with the formula in Crystal.

I grouped a date field {cpmain.courtdate} by year. I then inserted a summary in group section next to the year (count of cpmain.courtdate). This gave me the totals for each year and they are correct.

I have not been able to come up with a successful formula to calculate the difference between each year and then the percentage between years. Any help would be greatly appreciated.

I had previously tried to get this to work in a crosstab and was not able to do so.

Example from Excel.
Year Amt Difference %
1995 305
1996 255 -50 -16.4%
1997 363 108 42.4%
1998 381 18 5.0%
1999 463 82 21.5%
2000 580 117 25.3%
2001 709 129 22.2%
2002 727 18 2.5%
2003 1221 494 68.0%
2004 1274 53 4.3%
2005 1490 216 17.0%
2006 1656 166 11.1%
2007 2004 348 21.0%
2008 2160 156 7.8%
2009 2089 -71 -3.3%
2010 2398 309 14.8%

 

[kray4660]

I came up with a way using shared variables. There may be a better solution. But I know this works.
First formula, in the report header (or at least before the date group).

whileprintingrecords;
shared numbervar firstyear := distinctcount({cpmain.courtdate},{year}, "annually");

Second formula, in group footer
shared numbervar secondyr;
secondyr := distinctCount ({cpmain.courtdate}, {year}, "annually")

Third formula to do the math in the group footer.
shared numbervar firstyear;
shared numbervar secondyr;
numbervar difference;
shared numbervar percent;
difference:=secondyr-firstyear;
percent:= difference/firstyear*100;
firstyear:=secondyr;
difference;

Lastly to show the percent (again the GF).
shared numbervar percent;
percent

I hope this helps.

 

[wld1957]

Thanks for the quick response. I created the 1st formula and will put it in the report header. The formula has a problem with the "year" part as I do not have a field named "year".

whileprintingrecords;
shared numbervar firstyear := distinctcount({cpmain.courtdate},{year}, "annually");

If I leave {year} as listed: It returns the field is not known.
If I change it to year it returns not enough arguments have been given to this function.

If I replace year with cpmain.courtdate there is no error but the number is incorrect. I tried several variations after the field for cpmain.courtdate but could not get a correct result.

 

[kray4660]

Since I did not know what field you were grouping on, I used a filler field which I called {year}.

 

[wld1957]

Thanks, that's works great. Much appreciated.