Skip to main content
Solved

Percent Formula Help

EmberKrumwied
Forum|alt.badge.img+13

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?

Best answer by EmberKrumwied

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,"$")))

View original
Ember

MarkShnierYou
Forum|alt.badge.img+24

You gave an example of a formula which does work OK. Can you give us an example of a formula that does not work?

Mark Shnier. Your Quickbase Coach
EmberKrumwied
Forum|alt.badge.img+13

I thought I did...
(ToNumber([PY Value])-[Value])/(ToNumber([PY Value]))*100
doesn't work but 
(ToNumber([PY Value])-[Value])
does

Ember
EmberKrumwied
Forum|alt.badge.img+13

Actually, while it is "working" it doesn't return the correct value.

I just tried (ToNumber([PY Value])-[Value]) and the result is wrong.

PY Value is $2,040,000; Value is $2,080,800 so result should be -$40,800 instead it is showing as -$2,080,800 so my thought that my formula was working really isn't.

Ember
MarkShnierYou
Forum|alt.badge.img+24

It should work but maybe try this variation in case there's something funky with formula queries  

var number PYValue = ToNumber(

ToText(GetFieldValues(GetRecords("{143.EX.'"&[PY Key]&"'}"), 11)));

($PYValue]-[Value]) / $PYValue * 100

 

Mark Shnier. Your Quickbase Coach
EmberKrumwied
Forum|alt.badge.img+13

Result is still a blank field.

Ember
EmberKrumwied
Forum|alt.badge.img+13

It is interesting...

In my PY Value field (formula text field) that uses:
ToText(GetFieldValues(GetRecords("{143.EX.'"&[PY Key]&"'}"), 11))
returns the correct PY Value.

I tried to create a new field to just convert that result to a number field (formula numeric field) that uses:
ToNumber([PY Value])
the displayed result is 0.

Ember
EmberKrumwied
Forum|alt.badge.img+13

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,"$")))

Ember
Terms & ConditionsAccessibility statement