Algorithms
  • 13 Jun 2024
  • 3 Minutes to read
  • Contributors
  • 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

Description

Example

ABS(number)

Returns the absolute value of a number

ABS(a-b)

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

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

IF(condition,true_value,false_value)

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)

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"

dt(column)

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

a/dt(a)*100

rt(column)

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

a/rt(a)*100

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

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 (;).


Was this article helpful?