Common Operations & Advanced Formula Functions

Symbol	Alternate Equivalent Function	Operation
+	add(x,y)	Add two or more values, x + y
-	subtract(x,y)	Subtract two values, x - y
*	multiply(x,y)	Multiply two or more values, x * y.
/	divide(x,y)	Divide two values, x / y
()		Parentheses. Set the order of operations.

Symbol	Description
pow(x,y) Alternatively, use the symbol “^”	Calculates the power of x to y, x ^ y
abs(x)	Calculate the absolute value of a number x.
square(x)	Compute the square of a value, x * x.
sqrt(x)	Calculate the square root of a value.
cube(x)	Compute the cube of a value, x * x * x.
log10(x)	Calculate the 10-base logarithm of a value x.
log(x,y)	Calculate the logarithm of a value x with logarithm base y.
factorial(x)	Compute the factorial of a value. Factorial only supports an integer value as an argument.
max(a,b,c,…)	Compute the maximum value of a list with values.
min(a,b,c,…)	Compute the minimum value of a list with values.
mean(a,b,c,…)	Compute the mean of a list with values.
median(a,b,c,…)	Compute the median of a list with values.
mode(a,b,c,…)	Compute the mode of a list with values.
sec(x)	Calculate the secant of a value.
sin(x)	Calculate the sine of a value.
tan(x)	Calculate the tangent of a value.
%	Divide by 100.
? : ex: CONDITION ? TRUE_VALUE : FALSE_VALUE	Conditional Expression Ex: 15 > 100 ? 1 : -1 // returns -1
==	Equal Ex: 2 == 4 - 2 // returns true
=	Assignment Assign the value on the lefthand side of the equation to the variable on the righthand side Ex: a=5 // returns 5
compare(x,y)	Compare two numeric values. Returns the result of the comparison: 1 when x > y, -1 when x < y, and 0 when x == y.
compareText(x, y)	Compare two strings lexically. Comparison is case sensitive. Returns 1 when x > y, -1 when x < y, and 0 when x == y. Ex: compareText('B', 'A') // returns 1 compareText('2', '10') // returns 1 compare('2', '10') // returns -1
equal(x,y)	Test whether two numeric values are equal. Returns true when the compared values are equal, else returns false Ex: equal(2 + 2, 3) // returns false equal(2 + 2, 4) // returns true
equalText(x,y)	Check equality of two strings. Comparison is case sensitive. Ex: equalText('Hello', 'Hello') // returns true equalText('a', 'A') // returns false
unequal(x,y)	Test whether two values are unequal. Returns true when the compared values are unequal, else returns false Ex: unequal(2 + 2, 3) // returns true unequal(2 + 2, 4) // returns false

 

 Example Advanced Formulas:

Scenario: Tank Balance: Amount In - Amount Out when Amount Out is sometimes 0/not recorded

Background context:

• Liquid moves in and out of my tanks each day. We track the amount in and Amount Out for each tank to perform a balance of Amount In - (minus) Amount Out. However, on some days, there is only liquid going in and none coming out. 
  • Sometimes, the user may enter "0" to indicate there was no Amount Out
  • However, even if the user left Amount Out empty (null), we'd still want to know how much is going in as part of the balance and treat the Amount Out null value as 0
• The setup in Mapistry EDP includes:
  • A log to specify which tank is being logged, with a field for Amount In and a seprate field for Amount Out
  • The log formula will take the difference between Amount In and Amount Out

Log set-up:

Solution: Replace null values with 0 to use in formulas:

Two formula fields were created to show the difference. The best solution to treat null values as 0 is in #2. 

1. INCOMPLETE EQUATION: Balance Equation Without Considering "Amount Out" Empty Values (Nulls)
   1. Formula: AMOUNT_IN - AMOUNT_OUT
   2. Amount Out can be nothing some days. This formula will only produce an output if users enter "0" for Amount Out on those days. If the value for Amount Out is left empty/ null, this formula will not produce an output. See the other formula for solution
2. FINAL SOLUTION: Balance Equation Setting "Amount Out" Null Values = 0
   1. AMOUNT_IN - (AMOUNT_OUT ? AMOUNT_OUT : (0 gal))
   2. The formula above uses the conditional logic with ? : operators. Amount Out can be nothing. Users could enter a "0" but if they forget, and the value is null, we still want to know how much is going in as part of the balance and treat the Amount Out null value as 0. This formula checks whether there is a value for AMOUT_OUT. If there is, it uses the actual AMOUNT_OUT value. If there is no value, it uses 0 gallons and completes the calculation. 

Example Data in Views with both formula options:

Scenario: Tank liquid levels for different tank shapes

Background context:

• I need to track the liquid levels in cubic and spherical tanks to calculate the liquid volume. I can read the liquid level of each tank in feet.
• I need to:
  • (#1) I want to mark that the tank level is "Low" when the liquid level is read at <1 ft so we know we should refill the tank. 
  • (#2) I need to use the appropriate volume equation for cubes vs spheres to calculate the correct volume in each tank, according to its respective shape.
• The setup in Mapistry EDP includes:
  • A Resource for tanks, with a Resource property for the Tank Shape (Cube or Sphere), length/diameter, and volume when 100% full
    • Tank A is a cube shape
    • Tank B is a sphere
  • A log to track - for each tank, the liquid level (read in ft) periodically
    • The log formula fields calculates the liquid volume depending on the shape of the tank
    • The log formula field can also tell me if a tank is low (<1 ft)

Resource Set-up:

:

Log Set-up:

Solution to #1: Conditional statement based on numeric log entry value:

#1: I want to mark that the tank level is "Low" when the liquid level is read at <1 ft (in any tank) so we know we should refill that tank.

SOLUTION:

• Formula: Tank Level Low? = (LIQUID_LEVEL < 1 ft) ? "Low" : "OK"
• The formula above uses the conditional logic with ? : operators. The formula checks if the value for the Log Field LIQUID_LEVEL is <1. If it is, it returns "Low" and if the value is >1, it returns "OK"
• A View can be filtered to just the tanks with "Low" levels to identify which tanks are running Low. Additional counts & limits can be set on top of this configuration to trigger alerts when a Tank is low. 

EXTRA CREDIT:

What if I wanted to categorize the tank level 3 ways:

1. "Low" when the liquid level is <1 ft
2. "Warning" when the liquid level is <2 ft
3. "OK" when the liquid level is ≥ 2 ft

Solution (Extra Credit): Nested conditional statements based on numeric log entry value:

Formula: Tank Level Low? (w/ Warning) = LIQUID_LEVEL<1 ft ? "Low" : (LIQUID_LEVEL < 2 ft ? "Warning":"OK")

IMPORTANT: order matters. Write the statements in order that they should be checked and evaluated against. 

Solution to #2: Conditional statement based on string (text) Resource Property:

#2: I need to use the appropriate volume equation for cubes vs spheres to calculate the correct volume in each tank, according to its respective shape.

SOLUTION:

• Preliminary Formulas: (these are not required and can be written into the conditional statement itself. However, separating out the formulas first can help you validate that they are correct and provide context for each. Then the final formula can determine which formula to use depending on the TANK.SHAPE) - a formula to calculate volume for cubes, and another to caluclate volume for spheres
  • Cube - Liquid Volume = LIQUID_LEVEL * (TANK.LENGTHDIAMETER ^ 2)
  • Sphere - Liquid Volume = TANK.VOLUME_WHEN_100_FULL - (pi * (TANK.LENGTHDIAMETER - LIQUID_LEVEL) ^ 2 * ((0.5 * TANK.LENGTHDIAMETER) - ((TANK.LENGTHDIAMETER - LIQUID_LEVEL) / 3)))
• Formula: Final Volume = equalText(TANK.SHAPE, "Cube") ? CUBE_LIQUID_VOLUME : SPHERE_LIQUID_VOLUME
• The formula above uses the conditional logic with ? : operators. The formula checks if the value for the Resource's TANK.SHAPE resource property is = "Cube". If it is, it returns the result calculated by the "Cube - Liquid Volume" formula. If it is not, it returns the result calculated by the "Sphere - Liquid Volume" formula. 

 

 Example Results: