Calculations
You can set a text, long text, formatted text, number or date column in a Kahootz Database to be calculated instead of directly entered. This means that the values in that column are calculated based on other values in the entry each time it is saved - for example adding up a set of other number columns, or combining some text.
Your calculation can be a simple expression, such as adding or multiplying values together, or it can contain code and logic. There is a range of operators and functions you can use in your calculation. More information about the operators and functions you can use is given below.
If you add or update a calculation on a column, the existing database entries will be updated in the background; For a database with lots of rows, this can take some time. Calculations will also be updated when you add or change an entry.
> A calculation column is added after you've initially created a database by following these simple steps below.
1. Open the database you wish to add a calculated column too, under the "Actions" section, select "manage database"
2. Select the row you wish to apply an expression too, at the bottom under the "other options" section - tick the checkbox for calculated - shown below.
3. Add expression or code and click save - eg: please see example below.
* This would allow the following database below to add all the hours from each week day, to then totals them all together into the last column.
- Note: adding val ( {{column}} ) ignores any blank columns within the database
> Please see below for more details on formats, expressions & coding.
Basic Format
You can use the value of other columns in your calculation by putting the column name between {{ and }}. A list of the available column names will be shown on the add / modify column page when you're adding calculations, and you can click on them to insert them into the calculation.
You can use round brackets () to make sure your calculation is evaluated in the order you expect - so ( 2 + 4 ) / 2 will do the addition first, then the division - giving 3. Without the brackets, 2 + 4 / 2, normal mathematical precedence will apply, so it will do 4 / 2 first, then + 2, giving 4.
You can search and sort on calculated columns.
You can use one calculated column in another calculation. They are evaluated in column order, so if you want to use one calculation in another, make sure the first one is higher up the column order.
If there is an error from your code due to particular inputs for a row, the column will be set to blank.
Simple Expressions
Examples
Adding two number columns together: {{days on activity 1}} + {{days on activity 2}}
Multiplying two number columns together: {{cost per hour}} * {{total hours}}
Showing one number column as a percentage of another: {{hours spent on activity}} / {{total hours}} * 100
Showing that percentage as a whole number (no decimal places):int( {{hours spent on activity}} / {{total hours}} * 100 )
Work out the number of days between two dates: daysBetween( {{start date}}, {{end date}} )
Numbers
Numeric operators
You can use the following operators to combine numbers:
Addition | number1 + number2 | Example: To calculate the total number of days spent on activity1 and activity2: |
---|---|---|
Subtraction | number1 - number2 | Example: To calculate the cost after discount: |
Multiplication | number1 * number2 | Example: To calculate the total cost of a number of items: |
Division | number1 / number2 | Example: To calculate the cost per hour: |
Integer division | number1 \ number2 | How many times one number can be divided by another in whole numbers, ignoring the remainder. |
Division-remainder | number1 mod number2 | The remainder after dividing one number by another. |
Blank column/value | val ( {{column}} ) | In all these operators, if a numeric column/value is blank it'll be treated as an error, to treat empty columns as 0. |
Number Functions
You can use the following functions to manipulate numbers:
Absolute value | abs( number ) | The absolute value of a number is the number without a sign, eg: |
---|---|---|
Rounding | round( number ) | Gives the closest whole number, rounding up or down as nearest. |
Rounding up | ceiling( number ) | Gives the closest whole number, always rounding up. |
Rounding down | int( number ) | Gives the closest whole number, always rounding down. |
Maximum | max( number1, number2 ) | Return the maximum of number1 and number2. Only handles two numbers, not more. |
Minimum | min( number1, number2 ) | Return the minimum of number1 and number2. Only handles two numbers, not more. |
Text
Text Operator
Joining text | test1 & text2 | Note that this does not use + which is for adding numbers. You will also need to put in spaces explicitly where wanted. |
---|
Text Functions
Comparing text | compare( text1, text2 ) | Performs a case-sensitive or insensitive comparison of two text columns. |
---|---|---|
Find position | find( text_to_find, text ) | Finds the first occurrence of a text_to_find in text. find is case sensitive, findNoCase is not. |
Insert at position | insert( text_to_insert, text, position ) | Return text with text_to_insert inserted into text after character position. If position=0, it prefixes text_to_insert to text. eg: |
Remove from position | removeChars( text, start_position, num_chars ) | Return a text with num_chars removed starting at position start_position. |
Convert to lower case | lcase( text ) | Return text converted to lower case. |
Convert to upper case | ucase( text ) | Return text converted to upper case. |
Reverse | reverse( text ) | Return text in reverse order. |
Length of text | len( text ) | Return the length - how many characters - are in text. Includes spaces and other punctuation. |
Characters from left | left( text, num_chars ) | Return the leftmost num_chars characters of text. Counting includes spaces and other punctuation. |
Characters from right | right( text, num_chars ) | Return the rightmost num_chars characters of text. Counting includes spaces and other punctuation. |
Characters from position | mid( text, start_position, num_chars ) | Return num_chars of characters from text starting at position start_position.eg: |
Find and replace | replace( text, remove, insert [, scope] ) | Return text with occurrences of remove replaced by insert. If the scope is "1" then just the first occurrence is replaced. |
Substring until | spanExcluding( text, characters_to_exclude ) | Return characters from text, from the beginning until the first character in characters_to_exclude. The search is case sensitive, so if you want to stop at either A or a, then put both in characters_to_exclude. |
Substring until not | spanIncluding( text, characters_to_include ) | Return characters from text, from the beginning until the first character that is NOT in characters_to_include. The search is case sensitive, so if you want to include both A and a, then put both in characters_to_include. |
Trim spaces | trim( text ) | Return text with any leading and trailing spaces removed. |
Trim leading spaces | ltrim( text ) | Return text with any spaces at the beginning removed. |
Trim trailing spaces | rtrim( text ) | Return text with any spaces at the end removed. |
Convert to number | val( text ) | Return text converted to a number. Handles decimal places. Text that can't be returned to a number will cause an error, and thus a blank calculated column (but see conditional operator 'isNumeric() in the code section below) |
Dates and Times
Date values in the following functions can either be taken from columns (of date, date and time, month and year, entry creation date / date-time or entry modify date / date-time types) or entered as explicit dates in the format yyyymmdd - eg 20170401 is 1st April 2017
Time values in the following functions can either be taken from columns (of date and time, time, entry creation date-time or entry modify date-time types) or entered as explicit times in the format hhmmss
To show a calculated value in a 'date' column the result must be a valid date, but you can use the other result formats in text or number columns.
Date Functions - returning a number
Day of Week | dayOfWeek( date ) | Return a number for the day of the week of date in the range 1 (Sunday) to 7 (Saturday) |
---|---|---|
Day Of Year | dayOfYear( date ) | Return a number of the day of the year, in the range 1 (1st Jan) - 365 (31st Dec - or 366 in leap year) |
Days in Month | daysInMonth( date ) | Returns the number of days in the specified month (ie: 28, 29, 30 or 31) |
Days In Year | daysInYear( date ) | Return the number of days in the specified year (ie: 365 or 366 for leap years) |
Parts of a Date / Time | year( date ) | Return a number for the appropriate part of the specified date/time. Year is returned in four figures (2017); Month as 1-12; Day as 1-31; Hour in 24-hour notation as 0-23; Minute as 0-59 |
Days after | daysAfter( date1, date2 ) | Return the number of days that date2 is after date1. If date2 is before date1, a negative number is returned. |
Days between | daysBetween( date1, date2 ) | Return the number of days between date1 and date2. It doesn't matter which date is earlier, and will always return a positive number. If either is not a valid date, then empty text is returned. |
Date / Time Difference | dateDiff(datepart, date1, date2 ) | Return the number of "units" by which date1 is less than date2. datepart should be one of the following strings "yyyy": Years"q": Quarters (any 3 month period)"m": Months"d": Days"ww": Weeks"h": Hours"n": MinutesIf date2 is before date1, a negative number is returned. If either is not a valid date, then empty text is returned. |
Date / Time Comparison | dateCompare( date1, date2 ) | Return -1 if date1 is earlier than date2; Return 0 if date1 is the same as date2; Return 1 if date1 is later than date2; Accurate to the second if used with date-times or times. |
Current Date | now () | Uses the date the entry was last saved or updated of which can be used in various calculations, see below. |
For example, you have a database using a "date" column and you want to return the total number of days the entries have been open/outstanding.
You can use this function to show the elapsed days between the created date and today's date by adding "now ()" to the calculation as shown below.
Kahootz Tip: The example above will not update automatically, therefore, when you view the database the next day - the values will not have changed.
The calculation for "current date" uses the date of when the calculation was last saved/updated - (please remember this if you're going to use this value)
Date Functions - returning a date
Add to / Subtract from a date | dateAdd(datepart, number, date ) | Return a new date by adding the specified number of units to date. datepart should be one of the following strings "yyyy": Years"q": Quarters"m": Months"d": Days"w": Weekdays (Mon-Fri, skipping Sat and Sun. Simple addition, not aware of public holidays etc)"ww": Weeks"h": Hours"n": MinutesIf number is positive you'll get dates after date, ie: forwards in time. |
---|---|---|
Create Date | createDate( year, month, day ) | Create a date from three numbers, eg: CreateDate(2017,2,14) represents 14th Feb 2017 |
Create Date - Time | createDateTime( year, month, day, hour, minute, second ) | Create a date-time from six numbers, eg: CreateDateTime(2017,2,14,15,5,17) represents 14th Feb 2017 15:05:17 - just after 3pm |
Writing Code
As well as simple expressions, you can write code to make decisions.
There is a range of tags and logical operators for this. You can also use variables in your code to store intermediate values.
When you write code, you must set a variable called calcResult which will be displayed in the database cell.
Kahootz Tip: when writing code, there is a maximum character limit of 50,000
Tags
<SET var_name = expression> - Set the variable var_name to the result of calculating expression. Note that all variable names must begin with the string "var_".
Conditional Operators - return true or false, used in IF or ELSEIF conditions
The logical operators AND, OR and NOT are supported, returning true or false
value1 EQ value2 - Test if value1 equals value2 - works on both numbers and text (case insensitive)
value1 NEQ value2 - Test if value1 is not equal to value2- works on both numbers and text (case insensitive)
number1 GT number2 - Test if number1 is greater than number2
number1 GTE number2 - Test if number1 is greater than or equal to number2
number1 LT number2 - Test if number1 is less than number2
number1 LTE number2 - Test if number1 is less than or equal to number2
isNumeric( text ) - Test if text can be converted to a number - true if it can, false if it can't. (eg: can be used to check if something is a valid number, and explain the error if it can't rather than let the calculation fail and return blank.)
Using Conditions
| Executes CODE if the condition is true. |
| Executes one of the CODE blocks depending on which condition is true. If none of the conditions are true then the CODE following <ELSE> is executed (CODE4). You can have as many <elseif condition> blocks as you like, but only one <else>. |
Example
<SET var_daysAfterTargetDate = daysAfter( {{delivery date}}, {{planned delivery date}} )>
<IF var_daysAfterTargetDate EQ ""> <SET calcResult = "Bad date!">
<ELSEIF var_daysAfterTargetDate LT 0> <SET calcResult = "Early">
<ELSEIF var_daysAfterTargetDate EQ 0> <SET calcResult = "On time">
<ELSE> <SET calcResult = "Late">
</IF>