Shop

Your Notifications (0)

There are currently no notifications. You're all set!

Demos Trials
Knowledgebase
ChemDraw and ChemOffice+ Products Signals Notebook E-Notebook and ChemBioOffice Enterprise Spotfire and Signals Spotfire Products Image Artist Spectrus, Percepta, Katalyst, Luminata and more
Downloads
Downloads
Community
Ideas Portal Product Basics
About Support
Legal Resources Support and Maintenance Plans Product Life Cycle Support News
Contact Support

Add a calculation(s) to a Table template from the Signals Configuration

Katherine Benavides
  • Updated
For Administrators only.
Prior to this task:
✔ Table template must be created.
✔ Column(s) and header(s) of a Table template must be added/edited.
1️⃣ Signals Configuration ➡ select [Table Templates]
2️⃣ Table Templates ➡ select table template
3️⃣ Table template ➡ Columns ➡ "column name" ➡ select [Define Equation]
4️⃣ Define Equation window ➡ enter columns/headers plus function(s)
5️⃣ Define Equation window ➡ select [Save] 
6️⃣ Table template ➡ select Headers
7️⃣ Table template ➡ Headers ➡ "header name" ➡ select [Define Equation]
8️⃣ Define Equation window ➡ enter columns/headers plus function(s)
9️⃣ Define Equation window ➡ select [Save] 
🔟 Table template ➡ select [Save] 

 

Supporting Knowledge
Note: Available functions vary based on column type and are supported only for Text, Date/Time, and Number column types.
 
In the Define Equation window, the Available Columns/Headers section displays all columns and headers defined in the table template. Under the Functions section, users can view all supported mathematical functions, which can be combined with column/header values to define an equation in the Expression field. Users may input or select mathematical operators and values directly.

Once an equation is defined, a new field appears below it in the template form, where users can specify the calculated values by entering a number between 0 and 6.

If a column is defined as calculated, it becomes non-editable for the end user when that template is used to create a table.

Note: Column equations can use both column and header properties in their definitions, while header equations are limited to using only the header values, unless an aggregate calculation (AVG, SUM, STDEV, AVGOVER, SUMOVER, STDEVOVER) is used.

 
Available Functions: 
  • STDEV OVER – Its formula is: STDEVOVER(Arg1,Arg2), where Arg1 is a Number field and contains the numbers that the Standard Deviation is being calculated for, and Arg2 is a string-type field. The calculation will be the StdDev of the numbers that have identical values in Arg2.
  • SUMOVER – It sums the values from a particular column over the group of values where the values in a second column match.
  • Standard Deviation – It calculates the average and standard deviation of a set of values.

  • STDEVS Standard Deviation Sample assumes that the results are a Sample of the Population. The current STDEVOVER and STDEV use Standard Deviation Population; however, the variability of some data sets would be more accurately calculated using Standard Deviation Sample (STDEVS).

    STDEVS function lets you define an equation in the Header that will calculate the sample-based Standard Deviation Sample of a given numeric column with a single argument such as STDEVS(Arg1), where Arg1 is a numeric column.

  • STDEVSOVER – This is the aggregate calculation of Standard Deviation Sample.
  • AVGOVER – It calculates the average of values from a specific column over a group of matching values from a different column.
  • IF Statement Formulas – An IF statement is typically constructed in three parts: the first argument is the condition that needs to be met, the second is the value if the condition is met, and the third is the value if the condition is not met. Col1=IF(Arg1, Arg2, Arg3).
    IF [ColumnA] = “” allows the definition of behavior if the referenced cell has no value.
  • TIMEDIFF – It calculates the time duration between two Date/Time fields. General format: TIMEDIFF ([Start], [End], “unit”).

  • DATEADD – It allows the addition of a time duration to a Date/Time. A statement such as TIMEADD([Start], [Duration], “unit”) is used; supported units include “Millisecond,” “Second," “Minute”, “Hour,” “Day,” and “Week.”

    The function must be set up on a Date/Time property; likewise, the Start Time must be of type Time/Date. The duration must be of type Number; alternatively, a property of type Number with Unit with the Duration “measure” can be used in place of both the duration and units, following the format TIMEADD([Start], [Duration].

  • CONCATENATE – It allows multiple strings to be combined in a column of type Text. A statement such as CONCATENATE([ColumnA], “text”, [ColumnB]) will provide a results string with the relevant column values and strings as a single text string.

  • GETVALUE – This is the only function that accepts Number with Units. The function has 2 arguments: Argument 1 is a property of type Number w/Unit; Argument 2 is the unit for conversion. 

    The calculated value is a number (without a unit) that can be used in subsequent calculations. The unit can come from another column, entered string, or header; it reflects accepted “typed values” (the UI accepts, e.g., ug instead of µg). 
    For example: GETVALUE (Col1, g), if input is 100mg, output is 0.1.

  • ROWOFFSET – It allows values from a differing row to be used in a given row-based calculation. A statement such as ROWOFFSET([Column], offset) will provide the value from a previous or following row for the stated column, where an offset of -1 is the previous row.
  • ROUND – This rounds numbers up if the next digit is 5 or above and down if 4 or below.
  • ROUNDUP and ROUNDDOWN – These allow numbers to be rounded to the nearest unit, either Up (away from zero) or Down (towards zero). A statement such as ROUNDUP([Column], rounding), whereby the Column is a number and rounding is an integer, will round the number to the relevant unit A rounding of 0 rounds to ones, -1 to tens, -2 to hundred, 1 to tenths, 2 to hundredths, etc.
  • ROW – This calculates the row number.
  • PADSTART – This function is constructed as PADSTART(Arg1, Arg2, Arg3), where Arg1 is a number that is padded, and Arg2 defines the number of digits the number is padded to. The third argument is optional and allows definition of the character used for padding; if not set, a zero is used. This function will operate only on a property of type Text and can be included in a more complex equation.
  • CASE – It allows the admin to define a series of up to 20 different outcomes based on the results in a given comparison column. The first matching result is provided. The admin can define a final result should no outcomes match. This provides a more elegant alternative to nested IF statements.
  • Other functions available: SQRT, SUM, POWER, LOG, LN, EXP, √).

Related articles

Comments

0 comments

Article is closed for comments.