Algorithms
  • 21 Nov 2024
  • 8 Minutes to read
  • Dark
    Light

Algorithms

  • Dark
    Light

Article summary

An algorithm (also called a column algorithm) is a Data Block whose values are calculated with a formula based on other Data Blocks.

The following example is a report with a Layout having three data blocks:

  • Block "a" is Gross Sales of the year 2021

  • Block "b" is Gross Sales of the previous year

  • Block "c" contains an algorithm that calculates the percentage of variation using the formula  (a-b)/b*100.

contents/assets/images/assets/images/algo.png

Formulas use block letters to refer to other Data Blocks and can also refer to other algorithms. See the table below for a list of supported operators.

contents/assets/images/algorithms set up.png

A formula can return the following types of data:

  • Numeric (default)

  • Text

  • Date

  • Picture

Select the appropriate option from the Algorithm type dropdown list as shown.

contents/assets/images/algodatatype.B14.gif

The Picture option, (applicable to the Data View, Label, and Button Objects), allows you to display images in the Screen Object. For this to happen, the formula has to return the name of a picture file and the Picture option has to be selected.

Image files must first be uploaded to the Capsule. To upload an image, open the Capsule in Design mode and click on "Images" at the bottom of the left panel under "Resources". Once in the Images section, click on the blue  "+ " icon and follow the on-screen instructions. Press "SAVE" to save the image.

Example of images displayed in a Data View

contents/assets/images/assets/images/Picture Algorithm in Data View.png

The highlighted images are the result of a picture algorithm using the following formula:

contents/assets/images/objectsalgo.trend.png

In the formula, Block "g" refers to a Block that calculates the variance between the average order value and the previous year average order value. If that value is positive (greater than zero), the up-facing arrow is displayed. If that value is negative (less than zero), the down-facing arrow is displayed.

Supported file formats are .jpg, .gif, and .png. The maximum file size is 500 KB.

Supported arithmetic operators

The following table lists all supported arithmetic operators that can be used in formulas.

Type

Operator

Meaning

Example

Arithmetic

+

Addition

a+b

-

Subtraction

a-b

/

Division

a/b

*

Multiplication

a*b

%

Percentage

a/b% is equivalent to a/b*100

a*(1+15%)

^

Exponentiation

a^2 is equal to a*a


Comparison

=

Equal to

a=b

>

Greater than

a>b

<

Less than

a<b

>=

Greater than or equal to

a>=b

<=

Less than or equal to

a<=b

<>

Not equal to

a<>b


Text

&

Concatenation

a&b concatenates two text strings contained in Block "a" and Block "b"

a&”myText” concatenates the string contained in Block "a" with "myText".

When using this operator, the result of the algorithm is a text string therefore you must select the option Text from the Algorithm type dropdown list.

left(text,num_char)

Left substring

Returns the leftmost characters from a text string.

Left(a,4) returns the first four characters of the text contained in Block "a"

right(text,num_char)

Right substring

Returns the rightmost characters from a text string.

Right(a,4) returns the last four characters of the text contained in block "a"

mid(text,start_pos,num_char)

Substring

Returns the specified number of characters from a text string starting from the specified position.

Mid(a,2,4) returns the four characters starting from position 2 of the text contained in block "a"

Supported functions

The following table lists all supported functions that can be used in formulas.

Function category

Function

Syntax and description

Example

Board specific

dt

Syntax: dt(column)

Returns the column total. To use this function, the Layout must have an entity set by row

a/dt(a)*100

Board specific

rt

Syntax: rt(column)

Returns the row total. To use this function, the Layout must have an entity set by column

a/rt(a)*100

Board specific

gt

Syntax: gt(column)

Returns the grand-total. To use this function, the Layout must have an entity set by row and by column

a/gt(a)*100

Date and time

DATE

Syntax: DATE(year,month,day)

This function returns the DateTime object for a particular date, specified by the year, month, and day

@DATE returns present date

Date and time

DAY

Syntax: DAY(date)

This function returns the day number of the month (integer 1 to 31) that corresponds to the specified date

@DAY returns present day

Date and time

MONTH

Syntax: MONTH(date)

This function returns the month corresponding to the specified date value

@MONTH returns present month

Date and time

YEAR

Syntax: YEAR(date)

This function returns the year as an integer for a specified date

@YEAR returns present year

Date and time

TODAY

Syntax: TODAY()

Returns the current date

today()-a returns the number of days between the date in Block "a" and the current date, provided a date Cube is in Block "a"

today()+a returns a date resulting from the current date plus the number of days specified in Block "a"

Engineering

CONVERT

Syntax: CONVERT(number,from-unit,to-unit)

This function converts a number from one measurement system to its equivalent in another measurement system

CONVERT(a,day,hr) returns data from Block “a” converted from day format to hour format

Financial

IRR

Syntax: IRR(arrayvals,estimate)

This function returns the internal rate of return for a series of cash flows represented by the numbers in an array

IRR(a,x) where “a” is a Block and “x” is a number

Financial

YIELD

Syntax: YIELD(settle,maturity,rate,price,redeem,frequency,basis)

This function calculates the yield on a security that pays periodic interest

YIELD(a,b,c,d,e,f) where “a” and “b” are dates while “c”, “d”, “e” and “f” are numbers

Information

ISERROR

Syntax: ISERROR(cellreference); ISERROR(value); ISERROR(expression)

This function, Is Error of Any Kind, tests whether a value, an expression, or contents of a referenced cell has an error of any kind

ISERROR(a/x) where “a” is a Block and “x” is a number

Information

ISTEXT

Syntax: ISTEXT(cellreference); ISTEXT(value); ISTEXT(expression)

This function tests whether a value, an expression, or contents of a referenced cell has text data

ISTEXT(a) where “a” is a Block

Information

ISNONTEXT

Syntax: ISNONTEXT(cellreference); ISNONTEXT(value); ISNONTEXT(expression)

This function tests whether a value, an expression, or contents of a referenced cell has any data type other than text

ISNONTEXT(a) where “a” is a Block

Information

ISNUMBER

Syntax: ISNUMBER(cellreference); ISNUMBER(value); ISNUMBER(expression)

This function tests whether a value, an expression, or contents of a referenced cell has numeric data

ISNUMBER(a) where “a” is a Block

Logical

AND

Syntax: AND(logical conditions list)

Returns True if all conditions are True, otherwise it returns False

AND(a>0,b<100) returns True if the value in Block "a" is greater than zero and the value in Block "b" is less than 100

Logical

OR

Syntax: OR(logical conditions list)

Returns True if at least one condition is True. Returns False if all conditions are False

OR(a>0,b<100) returns True if the value in Block "a" is greater than zero or if the value in Block "b" is less than 100

Logical

TRUE

Syntax: TRUE()

This function returns the value for logical TRUE

TRUE(x>y) where “x” and “y” are numbers

Logical

FALSE

Syntax: FALSE()

This function returns the value for logical FALSE

FALSE(x>y) where “x” and “y” are numbers

Logical

NOT

Syntax: NOT(value)

This function reverses the logical value of its argument

NOT(x>y) where “x” and “y” are numbers

Logical

IF

Syntax: IF(valueTest,valueTrue,valueFalse)

Returns true_value if the condition is True and false_value if the condition is False The condition can be any logical expression

if((a*b)>0,a,c)

if(a>100,”Greater”, ”Lower”)

if(and(a>0,b>0),a*b,0)

Logical

IFERROR

Syntax: IFERROR(value,error)

This function evaluates a formula and returns a value you provide if there is an error or the formula result

IFERROR(a/x,error) where “a” is a Block, “x” is a number and “error” is the value in case of an error

Lookup

COLUMN

Syntax: COLUMN(reference)

This function returns the column number of a reference.

COLUMN(x)-1 returns the position of the column previous to x

Lookup

ROW

Syntax: ROW(reference)

This function returns the number of a row from a reference

ROW(x)-1 returns the position of the row previous to x

Lookup

INDEX

Syntax: INDEX(return,row,col,area)

This function returns a value or the reference to a value from within an array or range

INDEX(a,x,y,z) where “a” is a Block and “x”, “y” and “z” are numbers

Lookup

OFFSET

Syntax: OFFSET(reference,rows,cols,height,width)

This function returns a reference to a range. The range is a specified number of rows and columns from a cell or range of cells. The function returns a single cell or a range of cells

OFFSET(a,x,y) where “a” is the location from which to base the offset while “x” and “y” are numbers

Lookup

MATCH

Syntax: MATCH(value1,array,type)

This function returns the relative position of a specified item in a range

MATCH(x,a) where “x” is the value to search for and “a” is a Block

Math and trigonometry

ABS

Syntax: ABS(number)

Returns the absolute value of a number

ABS(a-b)

Math and trigonometry

ROUND

Syntax: ROUND(value,places)

This function rounds the specified value to the nearest number, using the specified number of decimal places

ROUND(a*b,2) return the result of the multiplication between “a” and “b” with 2 decimal places

Math and trigonometry

SUBTOTAL

Syntax: SUBTOTAL(functioncode,value1,value2,...); SUBTOTAL(functioncode,array)

This function calculates a subtotal of a list of numbers using a specified built-in function

SUBTOTAL(functioncode,x,y) where “x” and “y” are numbers and “functioncode” is a number as follows:

Built-in function

Function code

AVERAGE

1

COUNT

2

COUNTA

3

MAX

4

MIN

5

PRODUCT

6

STDEV

7

STDEVP

8

SUM

9

VAR

10

VARP

11

Math and trigonometry

SUM

Syntax: SUM(value1,value2,...); SUM(array); SUM(array1,array2,...)

This function returns the sum of cells or range of cells

SUM(a) returns the sum of the values in “a”

Math and trigonometry

SUMIF

Syntax: SUMIF(array,condition,sumrange)

This function adds the cells using a given criteria

SUMIF(a,a>0,b) where “a” and “b” are Blocks

Statistical

AVERAGE

Syntax: AVERAGE(value1,value2,...); AVERAGE(array); AVERAGE(array1,array2,...)

This function calculates the average of the specified numeric values

AVERAGE(a,b) where “a” and “b” are Blocks

Statistical

COUNT

Syntax: COUNT(value1,value2,...); COUNT(array)

This function returns the number of cells that contain numbers

COUNT(a) where “a” is a Block

Statistical

MAX

Syntax: MAX(value1,value2,...); MAX(array); MAX(array1,array2,...)

This function returns the maximum value, the greatest value, of all the values in the arguments

MAX(a) returns the highest value in “a”

Statistical

MIN

Syntax: MIN(value1,value2,...); MIN(array); MIN(array1,array2,...)

This function returns the minimum value, the least value, of all the values in the arguments

MIN(a) returns the lowest value in “a”

Text

CHAR

Syntax: CHAR(value)

This function returns the character specified by a number

CHAR(x) where “x” is a number

Text

CONCATENATE

Syntax: CONCATENATE(text1,text2,...)

This function combines multiple text strings or numbers into one text string

CONCATENATE(x,y) where “x” and “y” are numbers or text

Text

LEN

Syntax: LEN(value)

This function returns the length of, the number of characters in, a text string

LEN(a) returns the length of “a”

Text

MID

Syntax: MID(text,start_num,num_chars)

This function returns the requested number of characters from a text string starting at the position you specify

MID(x,3,4) returns four characters that follow the third letter in the text “x”

Text

RIGHT

Syntax: RIGHT(text,num_chars)

This function returns the specified rightmost characters from a text value

RIGHT(x,3) returns the last 3 characters on text “x”

Text

LEFT

Syntax: LEFT(text,num_chars)

This function returns the specified leftmost characters from a text value

LEFT(x,3) returns the first 3 characters on text “x”

Text

SUBSTITUTE

Syntax: SUBSTITUTE(text,old_piece,new_piece,instance)

This function substitutes a new string for specified characters in an existing string

SUBSTITUTE(x,y,z,3) returns the text “x” including “z” in place of the third “y”

Text

TRIM

Syntax: TRIM(text)

This function removes extra spaces from a string and leaves single spaces between words

TRIM(x) where “x” is text

Text

VALUE

Syntax: VALUE(text)

This function converts a text string that is a number to a numeric value

VALUE(two) returns “2”

The above examples use the comma as list separator. The list separator character may vary depending on your computer’s settings. By default, Board uses the list separator defined under Regional Settings in your computer's Control Panel. The US English version of Board uses a comma (,) for list separator by default, while other international versions may use a semicolon (;).

The complete list of supported functions and specific details can be found in the attached file at the bottom of this article.

It is the user's responsibility to understand how to apply the formulas appropriately in each context.


Attachments

Was this article helpful?