Summary

In this article, we will go through how to create custom formulas if they don’t fit into the other options outlined here: Data Inputs with Formulas

Create Custom Formula Data Inputs

Create custom formulas if they don’t fit into the other options.

Set up Variables

Create the variable you would like your device to refer to. A common starting variable is “x”.

One of the easiest ways to read a raw value from your Data Inputs is to use your variable “x” as the formula. This will represent the original value that the Data Input is receiving. This will allow you to view the last raw datapoint on the Data Inputs page and view raw datapoints from any reports or dashboards referencing this Data Input.

Some advanced Data Inputs can have multiple variables. An example of multivariable data points is included below. 

Multivariable

Multivariable Data Inputs if you want to calculate multiple data inputs at one time. Each variable must be an existing data input to select them from the dropdown. 

Ternary Conditionals 

Custom formulas also support ternary conditionals to apply basic logic to your data inputs. The basic structure is the following: 

x < 1 ? x : 0
CODE


This reads as "if the data input value is less than 1, report x. Otherwise report 0".

For example, if a user would like to ignore erroneous spikes in data from their third-party device, they can set this ternary operation to report large spikes as "0". The original data input values from the third party device may look like this:

mceclip0.png

 After the condition is applied to the formula the graph will look like the following without the erroneous spikes:

mceclip3.png

 You can also combine the multiple data inputs with your ternary conditional. The following is an example is utilizing the median value with the multivariable feature mentioned earlier. This may be used to set alerts or dashboard indicators if several conditions of different Data Inputs were being met at once. 

(median(a,b,c,d,e,f,g,h,i,j,k) > 11 && l < 5000) ? 1 : 0
CODE

 

mceclip4.png

 In this scenario, if the median of the listed data inputs is greater than 11 and the final data input is greater than 5000, set this data input to 1. Otherwise set the input to 0. This may be useful in alerting based off of multiple data inputs and their specific conditions in relation to each other.

Note: Creating complex or several layers of conditionals may impact alerting and other calculation processes.

Bit Masking values

Some Data Inputs will be reporting bit data from a Third Party Device. Bitmasking is a useful tool to extract the specific bit you would like your Data Input to refer to in order to accurately represent data being communicated from your Third Party Device.

The bitmasking formula is relatively simple and follows the following pattern (x&2^[bit#])/(2^[bit#]):  

Formula

Bit reference

(x&1)

Zero Bit (x&2^0)/(2^0)

(x&2)/2

First Bit (x&2^1)/(2^1)

(x&4)/4

Second Bit (x&2^2)/(2^2)

(x&8)/8

Third Bit (x&2^3)/(2^3)

(x&4096)/4096

Twelfth Bit (x&2^12)/(2^12)

Other Available Formulas 

Min with 4 values

 

Example Formula:

min(x,1,2,3)

Example Inputs:

{-3, -2, 0, 1, 2, 3},

Expected Outputs:

{-3, -2, 0, 1, 1, 1}

Max with 4 values

 

Example Formula:

max(x,1,2,3)

Example Inputs:

{0, 1, 2, 3, 4, 5}

Expected Outputs:

{3, 3, 3, 3, 4, 5}

Avg with 4 values

 

Example Formula:

avg(x,1,2,3)

Example Inputs:

{0, 1, 2, 3, 4, 5}

Expected Outputs:

{1.5, 1.75, 2, 2.25, 2.5, 2.75}

Median (even)

 

Example Formula:

median(x, 1, 2, 3)

Example Inputs:

{0, 1, 2, 3, 4, 5}

Expected Outputs:

{1.5, 1.5, 2, 2.5, 2.5, 2.5}

Median (odd)

 

Example Formula:

median(x, 1, 2, 3, 4)

Example Inputs:

{0, 1, 2, 3, 4, 5}

Expected Outputs:

{2, 2, 2, 3, 3, 3}

Mode

 

Example Formula:

mode(x, 1, 2, 2, 4)

Example Inputs:

{0, 1, 2, 3, 4, 5}

Expected Outputs:

{2, 1, 2, 2, 2, 2}

Mode (multiple)

 

Example Formula:

mode(x, 1, 2, 2, 1)

Example Inputs:

{0, 1, 2, 3, 4, 5}

Expected Outputs:

{1, 1, 2, 1, 1, 1}

Standard Deviation

 

Example Formula:

stddev(x, 1, 2, 3)

Example Inputs:

{2, 4, 9, 16, 25, 30}

Expected Outputs:

{0.7071067811865476, 1.118033988749895, 3.112474899497183, 6.103277807866851, 9.98436277385793, 12.144957801491119}

Square Root

 

Example Formula:

sqrt(x)

Example Inputs:

{2, 4, 9, 16, 25, 30}

Expected Outputs:

{1.4142135623730951, 2, 3, 4, 5, 5.477225575051661}

Floor

 

Example Formula:

floor(x)

Example Inputs:

{6, 4.1, 4.99, 4.999, 4.9999, 4.99999}

Expected Outputs:

{6, 4, 4, 4, 4, 4}

Ceiling

 

Example Formula:

ceil(x)

Example Inputs:

{6, 4.1, 4.99, 3.999, 3.0000000001, 4.4}

Expected Outputs:

{6, 5, 5, 4, 4, 5}

Absolute

 

Example Formula:

abs(x)

Example Inputs:

{-3, -2, 0, 1, 2, 3}

Expected Outputs:

{3, 2, 0, 1, 2, 3}

Round

 

Example Formula:

round(5.854123,x)

Example Inputs:

{0, 1, 2, 3, 4, 5}

Expected Outputs:

{6, 5.9, 5.85, 5.854, 5.8541, 5.85412}