Percent Formula Help
I have a table to track parcel values. Each parcel will/can have 1 record for each year. I am trying to calculate the percent change for each parcel between years. I have the following fields:
Parcel # - look up from another table
Current Year - number field to enter year
Previous Year - formula field to calculate previous year
CY Key - formula field to combine Parcel # and Current Year
PY Key - formula field to combine Parcel # and Previous Year
Value - numeric field to enter parcel value for the year
PY Value - formula field that returns the Value of the record where the CY Key equals the PY Key
I'm trying to create a % Change field formula: ((PY Value-Value)/PY Value)*100
The issue is, I think, the field types. The "Key" formula fields are Text fields, but the values I want to return are/should be numeric. So my formula for the PY Value is:
ToText(GetFieldValues(GetRecords("{143.EX.'"&[PY Key]&"'}"), 11)).
This formula correctly returns the PY Value (yay), but it is in a text field type. So in my % Change formula field I know I need to convert the values back to numbers but the formula below returns no results:
(ToNumber([PY Value])-[Value])/(ToNumber([PY Value]))*100
When I try just:
(ToNumber([PY Value])-[Value])
I do correctly get the difference between the two value fields, but as soon as I attempt to add any additional operations, the field returns no results.
Any idea of what I'm doing wrong in my formula so that I can get the percent change between the Value and PY Value fields?
I figured this out on my own. :) I couldn't understand why a similar use of the GetFieldValues formula worked on a different number field but wouldn't work on a currency field. I could only get the formula to work on the currency field if it was put into a text formula field. All attempts to simply convert the displayed value in the text field to a number resulted in displaying 0 or was blank.
I did notice that the text formula field would correctly display the located value but the GetFieldValues must act more like a snapshot of the matched field vs pulling the raw value. So what I was getting as a result was the number preceded by a $ (since my source field is formatted as currency it displays with the $) vs the raw actual number. So...
I searched for a way to remove the leading $ and updated my formula text field. The results was the raw number that then I could successfully convert to a number field. Since I like to have as few fields as necessary I then played with the formula so that I could place it into a numeric formula field and save myself a step.
Below is the final formula I used for any who are interested or may run into this issue themselves.
Formula - numeric field formatted as Currency:
var text PYValue =
ToText(GetFieldValues(GetRecords("{143.EX.'"&[PY Key]&"'}"), 11));
ToNumber(Left($PYValue,"$")& Right($PYValue,"$")))
