Avatar of rvfowler2
rvfowler2
Flag for United States of America asked on

FM - Adding Percent Increase to a Report

Attached see a FM report.  I want to have a column to the right of amount to show % increase/decrease from the prior year (each row is a fiscal year).  I can do this (and have done this) easily by creating a bunch of fields, but is there a way to create one field that will calculate per each row in a report?  I've been successful doing this with count and total fields via a report, but can't quite figure out how to do this with a calc field.  Thanks.
-PerIncrease.JPG
FileMaker Pro

Avatar of undefined
Last Comment
rvfowler2

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Will Loving

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Will Loving

Sorry, typo there. The calculated date should obviously be "11/30/05" not "1/30/05". You may also need other predicates in the relationship to find the correct record you want to relate to but that shouldn't be too hard.
Will Loving

Randy - Attached is a file which does the trick. It even takes into account Leap Year when the last day of Feb is the 29th rather than the 28th. Here's the date calculation the accommodates either the current or previous year being leap year:

PreviousEndOfMonth =
Date( Month( MonthEndDate ) ;
Case( Month( MonthEndDate ) = 2 ; Case( Mod( Year( MonthEndDate ) - 1 ; 4 ) = 0 ; 29 ; 28 ) ; Day( MonthEndDate ) ) ;
Year( MonthEndDate ) - 1 )

There's another way to calculate the last day of any month but in this case because you're relating year to year, either of which could be a leap year, this is better.
Prev-Year-Compare.fp7
rvfowler2

ASKER
Will, it worked!  99% of the way there, but may negative numbers don't look right.  None of them ever get over -100%.  Do I need to take the absolute value when I subtract below?:

(BldgEscs_Active::Amount - BldgEscs_PriorYr::Amount) / BldgEscs_PriorYr::Amount
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
rvfowler2

ASKER
IN case you need to see my relationships.  The report is based on the main TO, not the PriorYr TO
-PriorYearChange.JPG
Will Loving

It looks to me like the prior year calculation should be on the left not on the right, otherwise you are looking forward a year not back.
Will Loving

The calculation looks correct and I don't think you need to use the absolute value unless you want the percent change to always be positive which could be misleading.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
rvfowler2

ASKER
First, thanks for year calc, but I made it easy on myself and converted every date to a 4-digit year and resolved it as a number.  Second, Yes, I already fixed the Relationships.  It was obvious once I looked at the report.  Third, OK, see it now.  A % change in the negative of the prior year can never be more than 100% because, for example, if it drops to 0 that would be a 100% drop.  It would have to go into a negative number to be over 100% and in this case, since it is taxes, it won't.  Awarding Points.