Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.
SOLVED

Calculate variance

Level 2

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!

1 Accepted Solution

Former Community Member

The script shoudl be:

if (NumericField1[1] ne 0) then

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

endif

8 Replies

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

Level 2

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

Level 2

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

endif

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

Paul

Level 2

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

The script shoudl be:

if (NumericField1[1] ne 0) then

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

endif

Level 2

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

The following has evaluated to null or missing: ==> liqladmin("SELECT id, value FROM metrics WHERE id = 'net_accepted_solutions' and user.id = '\${acceptedAnswer.author.id}'").data.items [in template "analytics-container" at line 83, column 41] ---- Tip: It's the step after the last dot that caused this error, not those before it. ---- Tip: If the failing expression is known to be legally refer to something that's sometimes null or missing, either specify a default value like myOptionalVar!myDefault, or use <#if myOptionalVar??>when-present<#else>when-missing. (These only cover the last step of the expression; to cover the whole expression, use parenthesis: (myOptionalVar.foo)!myDefault, (myOptionalVar.foo)?? ---- ---- FTL stack trace ("~" means nesting-related): - Failed at: #assign answerAuthorNetSolutions = li... [in template "analytics-container" at line 83, column 5] ----