% Increase Formula

[stormtrooper]

Hello there. I must be having one of those "brainfart" days. But I need a formula in which will yield me a percent increase from the previous fiscal year. Here is what I have.


Grp: Fiscal Year - 2003
Dtl: Hourly Rate - 12.00

Grp: Fiscal Year - 2004
Dtl: Hourly Rate - 15.00


This is for CR9 by the way.

Thanks

 

[lbass]

Is there really only one detail (hourly rate) per fiscal year? If so, you could remove the fiscal year group and use a formula like:

if onfirstrecord or
{table.fiscalyear} = previous({table.fiscalyear}) then 0 else
({table.hourlyrate}-previous({table.hourlyrate})) % previous({table.hourlyrate})

-LB

 

[stormtrooper]

No, the report is not really that straight forward. Here is what it's like.

GrpHdr: Category
GrpHdr2: Region
GrpHdr3: Fiscal Year
Dtlsuppressed) Hourly Rate
GrpFtr2: Avg. Hourly Rate

eg. Category: School
Region: North
Fiscal Year: 2003
Avg.Hrly Rt: 12.50

Category: School
Region: North
Fiscal Year: 2004
Avg.Hrly Rt: 15.00 % Inc. from Prev. FY: 16.7%

The above 16.7% was obtained by using

915.00 - 12.5 / 15.00) x 100

Thanks

 

[lbass]

You could create two running totals using the running total editor:

For {#2003}, select {table.hourlyrate}, average, evaluate based on a formula:

{table.fiscalyear} = 2003

Reset on change of group (region). For {#2004}, just change the formula in the evaluation section to:

{table.fiscalyear} = 2004

Then create a formula in the formula editor:

({#2004}-{#2003}) % {#2003}

This would need to be placed in the group footer for region.

-LB

 

[stormtrooper]

I created my own running totals as I need the count of the totals that are zero or null so that they are not included in the average calculations.

For some reason I put the Fiscal Year group in #3. Here is the current version.

I'm still looking for the % increase from the previous year.

GrpHdr_1: Fiscal Year (spans back 5 years)
GrpHdr_2a: Category Headings
GrpHdr_2b: Category
GrpHdr_3a: Region
GrpHdr_3b: (suppressed)
Contains the following formula:

{@Initialize_count} =
whileprintingrecords;
numbervar non_null_count:= 0;

Dtl_a: (suppressed)
Contains the following formula:

{@Increment_count_hourly_rate} =
whileprintingrecords;
numbervar non_null_count;

if {@Hrly_Rt} > 0
then non_null_count_:= non_null_count + 1;

Dtl_b: (suppressed)
Contains the following formula:

{@Hrly_Rt} =
if {SALARY.CLASS} = "ABC123" and
{SALARY.POSITION} = "Director"
then {SALARY.RATE_PER_HR}
else 0

GrpFtr_3a: (suppressed)
Contains the summary (sum) of {@Hrly_Rt}

GrpFtr_3b: Contains formula for {@Avg_hourly_rate} =

if {@Non_Zero_Count} = 0
then 0
else Sum({@Hrly_Rt},{TABLE.REGION})/
{@Non_Zero_Count};

Here's how the report could look like:

YEAR DIRECTOR
CATEGORY A
REGION 1 HOURLY RT %Chng
2002 12.50


YEAR DIRECTOR
CATEGORY A
REGION 1 HOURLY RT %Chng
2003 12.50


YEAR DIRECTOR
CATEGORY A
REGION 2 HOURLY RT %Chng
2003 10.00


YEAR DIRECTOR
CATEGORY A
REGION 1 HOURLY RT %Chng
2004 9.50


YEAR DIRECTOR
CATEGORY A
REGION 2 HOURLY RT %Chng
2004 15.00



I'm having the toughest time finding the % change value.

Thanks.

 

[lbass]

You have entirely changed your report, and it seems to me that your first layout made more sense. Can you please explain what you want to compare here? Are you trying to compare hourly rates by fiscal year for each position? Your latest version looks like you don't care about individual positions so much as regions.

Using your original design, where fiscal year is the inner group (Grp#3), and using the running total editor, you could check for nulls in a formula like {@hrlyrate}:

if isnull({table.hourlyrate}) then 0 else {table.hourlyrate}

Use this as the field to average, and then in the evaluate based on a formula section, you would enter:

year({table.date}) = 2003 and
{@hrlyrate} <> 0

Assuming your Grp#1 is category and Grp#2 is region, choose reset on change of group->Grp#1: category.

Then repeat for 2004 changing the year in the evaluation formula. Then use a formula like the following (to be placed in the group #1 footer) to show the percent difference:

if (isnull({#2003}) or
isnull({#2004})) then 0 else
if {#2003} > 0 then
({#2004}-{#2003})%{#2003} else
0

-LB

 

[stormtrooper]

lbass. Thanks for all your help! The "higher-ups" here decided that the % increase will now be calculated using Excel. They also change or add to the report requirements more than most people change underwear.

Anyway, the main comparison was to find out what the % increase was in the hourly wage from the previous fiscal year within the same category and region. I also had to use the incrementing formulas because zero values were not to be included in the averages. eg. 0, 1, 2, 0, 3 has an average of 2 and not 1.2.

Again, thanks for the help.