Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

FM - Adding Percent Increase to a Report

Posted on 2011-03-04
8
790 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:rvfowler2
  • 5
  • 3
8 Comments
 
LVL 25

Accepted Solution

by:
Will Loving earned 500 total points
ID: 35038840
One way to do it would be to have a relationship from field in each record that calculates the date of the previous year - e.g. for "11/30/06" the calculation would have "1/30/05" and then create a relationship from the calculation field to the date field. Once you have the relationships setup, you can create a calc field which will determine the percentage change between the two years.
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 35038987
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.
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 35039393
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
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 2

Author Comment

by:rvfowler2
ID: 35039533
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
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 35039580
IN case you need to see my relationships.  The report is based on the main TO, not the PriorYr TO
-PriorYearChange.JPG
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 35039669
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.
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 35039684
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.
0
 
LVL 2

Author Closing Comment

by:rvfowler2
ID: 35039932
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
FM Exporting Pictures In Container 1 1,028
Filemaker xsl file 8 138
resizing an image in a container field 3 78
Export Filemaker Pro Database, All Tables, All Fields 7 495
Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question