Formulas

 > Widgets > Formulas

Creating Formulas #

Formulas allow you to perform advanced calculations and/or apply functions over your data without having to know any SQL or scripting language.

You can make them when you are creating or editing your widgets, and also in the Edit Data Source page.

After you have chosen your Data Source, created formulas will appear as the last item of your tables.

To create a formula you need to click on the fx button.

Formula Access 1

 

The formula creator window will appear, to create a formula you need a valid expression and a unique name.

On the right you will have three types of expressions, each type in each group.

  • Columns: are your data source columns.
  • Operators: are mathematical operators.
  • Functions: are mathematical functions.

Create Formula 1

 

There are a couple of tools to help you build your expression:

  • Typing manually in the formula text area causes an auto complete to help you.
  • Drag & drop items from the columns or function groups to the formula text area.
  • Double clicking an item from the columns or function groups will write automatically to the formula text area.
  • You can find a specific column or function by using the search bars.

To use a column on your formula you will have put quotation marks in table and column name and joining them with a dot.

  • "Products"."Unit Price": column Unit Price of the table Products.

To use a function on your formula you will have to use the function name followed by a parentheses.

  • AVG( ): average function.

This example applies a average function on price column:

  • AVG( "Products"."Unit Price" )

Create Formula 2

 

If you type a column or a table name you will notice that a auto complete will help you, when you press enter with a column highlighted it will automatically write for you the table and column name with the quotation marks.

Create Formula 3

 

Auto complete will also work with functions.

Create Formula 4

 

Note
Before you can Save a formula you will always have to Validate it fist, only then the save button will be available.

 

Formula Syntax #

Formulas allow you to perform advanced calculations and/or apply functions over your data without having to know any SQL or scripting language.

This is a comprehensive list of every formula operations and functions:

Operators

Function Description Example
+ Adds two values. "Products"."Unit Price" + "Products"."Discount"
- Subtracts two values. "Products"."Unit Price" - "Products"."Discount"
* Multiplies two values. "Products"."Unit Price" * 1.23
/ Divides two values. "Products"."Unit Price" / "Products"."Discount"
^ Exponents a value. "Products"."Unit Price"^2

 

Functions

Function Return Type Description Example
ABS Numeric Returns the absolute value. Parameter must be number. ABS(-8)
AVG Numeric Returns the average. Parameter must be number. AVG("Products"."Unit Price")
COUNT Numeric Counts the number of rows. COUNT("Suppliers"."Address")
COUNTD Numeric Counts the number of distinct rows. COUNTD("Suppliers"."Address")
DATE Date Returns the date representation of the text parameter. For date representations saved as a string, so it can be used for aggregations or mathematical operations. Date must be in: (yyyy-MM-dd) DATE('2018-01-01')
DATE_DIFF Numeric Returns the difference between two dates. Result in days. DATE_DIFF("Orders"."Shipped Date","Orders"."Required Date")
LOWERCASE Text Returns the text in lowercase. LOWERCASE("Customers"."Address")
MAX Numeric Returns the largest value. Parameter must be number. MAX("Products"."Unit Price")
MEDIAN Numeric Returns the median. Parameter must be number. MEDIAN("Products"."Unit Price")
MIN Numeric Returns the smallest value. Parameter must be number. MIN("Products"."Unit Price")
NOW Date Returns today's date and time. NOW()
STRING Text Returns the text representation. Must be of type numeric. STRING("Products"."CategoryID")
SUM Numeric Adds all the numbers. Parameter must be number. SUM("Orders"."EmployeeID")
TODAY Date Returns today's date. TODAY()
TOTAL Numeric Returns the number of records of a given column. Parameter must be number. TOTAL("Orders"."EmployeeID")
UPPERCASE Text Returns the text in uppercase. UPPERCASE("Employees"."LastName")

 

Editing Formulas #

To edit a already existing formula click on the edit button of a specific formula.

Formula Access 2

 

This will bring up a new windows to edit your formula, don't forget to validate it again if you change the expression.

Create Formula 5

 

Deleting Formulas #

To delete a formula you need to go to Data Sources and choose the Data Source which has the formula you want to delete.

 

You must choose the tab Formulas.

To delete a formula click on the button, you must also confirm this action.

Deleting Formulas 2

 

Notice that you can also edit and create new formulas (Create Formula) in Edit Data Source Page page.

Still need help? Get in touch!
Last updated on 16th Oct 2018