### 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:`{{days on activity1}} + {{days on activity2}}` |
---|---|---|

Subtraction | number1 - number2 | Example: To calculate the cost after discount:`{{Original cost}} - {{discount}}` |

Multiplication | number1 * number2 | Example: To calculate the total cost of a number of items:`{{Cost per item}} * {{number of items}}` |

Division | number1 / number2 | Example: To calculate the cost per hour:`{{Total cost}} / {{number of hours}}` |

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. eg: `5 mod 2 = 1 (2*2=4, with 1 remaining)` |

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. Use this expression - please refer to the example/screenshot above. |

__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: `abs(2) = 2` and `abs(-2) = 2` |
---|---|---|

Rounding | round( number ) | Gives the closest whole number, rounding up or down as nearest. eg: `round(1.1) = 1` and `round(1.9) = 2` Halves will be rounded to the nearest even number to avoid bias eg: `round(1.5) = 2` and `round(2.5) = 2` and `round(3.5) = 4` |

Rounding up | ceiling( number ) | Gives the closest whole number, always rounding up. eg: `ceiling(1.1) = 2` and `ceiling(1.9) = 2` |

Rounding down | int( number ) | Gives the closest whole number, always rounding down. eg: `int(1.1) = 1` and `int(1.9) = 1` |

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 )compareNoCase( text1, text2 ) | Performs a case-sensitive or insensitive comparison of two text columns. Return a negative number if text1 is less than text2; returns 0 if text1 is equal to text2; returns a positive number if text1 is greater than text2. |
---|---|---|

Find position | find( text_to_find, text )findNoCase( text_to_find, text ) | Finds the first occurrence of a text_to_find in text. find is case sensitive, findNoCase is not.Returns the position of text_to_find in text; or 0, if text_to_find is not in text |

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:`insert(" My ","Hello Friend",5) returns "Hello My Friend"` |

Remove from position | removeChars( text, start_position, num_chars ) | Return a text with num_chars removed starting at position start_position.eg: `removeChars("Hello Friend",5,7) returns "Hello"` |

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.eg: `reverse("kahootz") returns "ztoohak"` |

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: `mid("kahootz",3,4) returns "hoot"` |

Find and replace | replace( text, remove, insert [, scope] ) replaceNoCase( 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.If the scope is "ALL" then all occurrences are replaced.(Versions using Regular Expressions for very advanced use are available - ask support!) |

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.eg: `spanExcluding("kahootz.doc",".,/") returns "kahootz"` |

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.eg: `spanIncluding("aardvark","aeiou") returns "aa"` |

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 )month( date )day( date )hour( time )minute( time ) | 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.If either is not a valid date, then empty text 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 - "yyyy": Years
- "q": Quarters (any 3 month period)
- "m": Months
- "d": Days
- "ww": Weeks
- "h": Hours
- "n": Minutes
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, therefore, when you view the database the next day - the values will not have changed.will not update automatically

The calculation for "current date" uses the date of when the calculation was last saved/updated - (pleasethis if you're going to use this value)remember

__Date Functions - returning a date__

Add to / Subtract from a date | dateAdd(datepart, number, date ) | Return a new date by adding the specified - "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": Minutes
number is positive you'll get dates after date, ie: forwards in time.To go backwards in time use a negative value for number. |
---|---|---|

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.

### 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

`<IF ` | Executes CODE if the condition is true. |

`<IF ` | 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>

## Share