Calculate variance

[carojvi]

Hello,

This is most likely a simple question... I'm trying to calculate a variance %. I have the script editor open, I picked "calculate" under "show" and I'm left with this:

form1.#subform[1].NumericField1[2]::calculate - (FormCalc, client)

I obviously don't know anything about script, so what do I add in there?

I want this calculation:

(NumericField1[1]-NumericField1[0])/NumericField1[1]

or

(a-b)/a where a=NumericField1[1] and b=NumericField1[0]

Thank you!

[Former Community Member]

I would rename my fields to give them unique names (it makes live easier in scripting as well as helping you

understand what the script is doing. So lets say you rename the NumericField[1] to A and the NumericField[0] to B.

The first line that appears there is a description of what event and what object you are coding against as well as the language being used and th elocation where the script will execute. On the next blank line enter in

A - (B/C)

The field that you are executing the script on will recieve the result of this calculation.

Hope that helps

Paul

[carojvi]

Thanks, it works.

Now when I fill the form though, how do I get rid of this annoying message when I type in the 1st number?

Once I hit OK, it's fine, but it breaks up the flow of filling out the form...

Thanks!

[Former Community Member]

NumericField1[1] is 0 to start so you are dividing by 0 ....you can either test to see if that field is not 0 and then do the calc or set a default value of 1 in that field.

Paul

[carojvi]

Thanks. If I set 1 as default value, the form opens with that screen (I assume I'd have to set both values to 1 as default).

How would I go about testing the values? Is there a code that would work like an Excel "IF" function, to where the calculated field would 'wait' until the values are filled in to calculate?

[Former Community Member]

if (NumericField1[1] ne 0) then

     enter your equation here

endif

Now your calculation will not execute until you have a value that is not 0 in the field

Paul

[carojvi]

Thanks. This is what I have now:

if

(NumericField1[1] ne 0) (NumericField1[1]-NumericField1[0])/NumericField1[1]*100 endif

The script editor is telling me there is an error at col 27, which is where my formula starts. Is there supposed to be a comma, or anything?

[Former Community Member · Accepted solution]

The script shoudl be:

if (NumericField1[1] ne 0) then

      (NumericField1[1]-NumericField1[0])/NumericField1[1]*100

endif

[carojvi]

Thanks Paul, didn't know 'then' was part of it