Calculations: If one field is empty, return a value instead of NaN
Hi,
Here is my calculation to return Days on Market:
var d1 = moment(@Sold Date);
var d2 = moment(@On Market Date);
var result = d1.diff(d2,'days').toFixed()
result;
At the moment, if the Sold Date field is empty, the calculation returns NaN.
I need to figure out how I can return a text field like "Not Sold Yet" or, just return an empty field.
Can anyone help?
Thanks!

Hi Wernard,
var d1 = moment(@Sold Date);
var d2 = moment(@On Market Date);
var result = d1.diff(d2,'days').toFixed()
d1 != null && d2 != null ? result : "Not Sold Yet"But this result is a string, not a number (using .toFixed() makes it already a string; e.g you can't do @sum of this field in another app). Means: If you want to do further calculations with the result you have to parse it to number.
Rainer

Hi Rainer,
Thanks SO much! You are brilliant...
I think I may have to use this field as a number, as I have to calculate an average "days on market"
So, if you have some time:.
If I rather want to leave the result empty if "Sold Date" is empty (so that result is a number)  what will the calculation look like?
Thanks!
Wernard

Create a new calculation field and delete the existing one cause it's already text type (and you can't change the type).
Then:var d1 = moment(@Sold Date);
var d2 = moment(@On Market Date);
var result = d1.diff(d2,'days');
d1 != null && d2 != null ? parseInt(result) : """" at the end leaves the field empty, if you add 0 instead of "" it will show 0.
Rainer

Once again  thanks  these options somehow do not always show up in calculation fields, but I got it working though.
I have another problem: I want a calculation field to display "some text" (say: "Unconditional") only if a date field is filled in  or  visa versa : if the date field is empty, I need it to display a different text of my choice....
Example: Date field is called "Unconditional Date". When a date is entered, then calculated field (named "Sold Status" must display "Unconditional"
Can you help (again;))
Thanks!
w

Thanks Rainer....
Â
What will this look like if we want the field to display text  conditional to more than one field:
Say:
@Unconditional Date != null ?Â "Unconditional" : "Other text"
AND
@Sale Date != null ?Â "@Sale Date" : "Other text"
Also:
@Unconditional Date != null ?Â "Unconditional" : "Other text"
OR
@Sale Date != null ?Â "@Sale Date" : "Other text"
Also, instead of null, what will it look like if we want the condition to be a certain value
(Sold in the example below):
@Field1 != "Sold" ?Â "My Text" : "Other text"
Sorry for all the extra work I'm giving you  I actually have spent a couple of hours trying to find solutions on the blog....
w

Hi Wernard,
@Field1 != "Sold" ? "My Text" : "Other text"
seems ok  if field1 is a text field or a single choice cagtegory field. If != "Sold" (which means also: if empty) it shows "My Text", only if field 1 exactly == "Sold" it shows "other text".Your other conditions I don't understand. What shoud be shown if
 both date fields != null
 Sales Date != null but Unconditional == null
 Sales Date == null but Unconditional != null
 both == null
"AND" is in JS &&, "OR" is 
Rainer

Thanks once again..
I will come back to you if I need further help with these, but I now have an urgent one to solve (I have spent hours trying to make this work!
See the screenshot... I need to add up the calculated amounts in the two fields: "Our Listing Fee", and "Our Selling Fee" and put the result in the " Total Expected Fee" field. My calculations in these two fields give the correct results  all I need to do is add the two figures together. Darn if I can get it to work...
Your help as always will be greatly appreciated...

In the two fields "Our Listing Fee" and "Our Selling Fee" you have text (cause you use .toFixed() and you added "$"). Before you can calculate with the amounts you have to strip off "$" and parse the amounts (which still are text) to numbers.
In the " Total Expected Fee" field enter:
var a = @Our Listing Fee.replace(/[^09]/g,"");
var b = @Our Selling Fee.replace(/[^09]/g,"");
Number(a) + Number(b)Rainer

You're a genius...
Regarding:
var a = @Our Listing Fee.replace(/[^09]/g,"");
var b = @Our Selling Fee.replace(/[^09]/g,"");
Number(a) + Number(b)The result ended up being : 3,154,000.00  it should be $31,540.00
So we are almost there... but all I need now is for the result to be divided by 100, and for it to have a $ in front, and two decimals...?
Thanks so much,
w

Hi Rainer,
I worked it out! This gives me the right result:
Total Fee is:
var a = @Our Listing Fee.replace(/[^09]/g,"");
var b = @Our Selling Fee.replace(/[^09]/g,"");
"$" + ((Number(a) + Number(b))/100).toFixed(2).replace(/\B(?=(\d{3})+(?!\d))/g, ",")You may have a simpler code, but this works for me now  thanks for your amazing help!
w

"$" + ((Number(a) + Number(b))/100).toFixed(2).replace(/\B(?=(\d{3})+(?!\d))/g, ",")
is the same code you've already used in the 2 other calculation fields. It's the right code, I've no shorter one. But keep in mind that you've made it again a text. For further calculations you've to parse it again to a number.

Hi Guys,
I think this is simple but I really hope you can help. I have this calculation:
var program = @Name of Loan Program FHA Hide
var financing = @Financing
financing == "FHA (FHA Financing Addendum)" ?
(program == null ? "FHA" : program) : nullI wanted the calculation field to return a null value based on the above conditions. However, it returns an error saying null must be string. I tried adding .tostring to the null value but to no avail. How do I get around this?
Thanks for your help!

Hi Guys,
I have this calculation for NPS Result. I wanted the NPS Result calculation field to return a value "No NPS Feedback Available" if the NPS Score = 0 but if the NPS Score != 0 then return the current value of NPS Score. I want to ask how can I return this string value if NPS = 0? Can someone help me?
Thank you in advance!

Hi Mariane,
a calculation field can be one of 3 types: number, String and date. Once a field is saved as one of these types the type can't be changed. You have to decide if your field should be a type number (for results > 0 ) or type string (for "No NPS Feedback availabel"). If type number you can use that field for further math calculations, if type string you can do that too, but it needs some parsing.
If you are sure that it should be a string I assume you should create a new calc field with this code:
@NPS Score > 0 ? String(@NPS Score) : "No NPDS Feedback Available"
I assume the field NPS Score is a real number field so you can use > 0Rainer
Please sign in to leave a comment.
Comments
21 comments