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.
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.
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" )
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.
Auto complete will also work with functions.
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.
This will bring up a new windows to edit your formula, don't forget to validate it again if you change the expression.
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.
Notice that you can also edit and create new formulas (Create Formula) in Edit Data Source Page page.