The following functions are available for creating Calculations in TapClicks. NOTE: Items in bold are required.
| Name | Format | Description |
| SUM | SUM(value1, [value2, …]) | Sum of a series |
| SUM_IF | SUM_IF(value, [operator] comparison[, value override]) | The sum of a series with a condition |
| AVERAGE | AVERAGE(value1, [value2, …]) | Average of a series |
| MIN | MIN(value1, [value2, …]) | Minimum value of a series |
| MAX | MAX(value1, [value2, …]) | Maximum value of a series |
| ROUND | ROUND(value) | Rounds to nearest integer |
| FLOOR | FLOOR(value) | Largest integer < the value |
| CEIL | CEIL(value) | Smallest integer > the value |
| COUNT | COUNT(value1, [value2, …]) | Number of values in a series |
| UNIQUECOUNT | UNIQUECOUNT(value1, [value2, …]) | Number of unique values in a series |
| UPPER | UPPER(text) | Converts text to upper case |
| LOWER | LOWER(text) | Converts text to lower case |
| SUBSTRING | SUBSTRING(text, start, length) |
Extracts parts of a text, beginning at the position specified (start) and returning the specified number of characters (length) |
| TRIM | TRIM(text) |
Removes leading, trailing, and repeated spaces in text |
| CONCAT | CONCAT(text1, text2, ...) |
Joins two or more text strings |
| GROUP_CONCAT | GROUP_CONCAT(value[, separator]) | Joins together values across rows from one column |
| REPLACE | REPLACE(text, old_text, new_text) |
Replaces part of a text with a different text |
| REPLACE_REGEX | REPLACE_REGEX(text1, text2, text3) | Finds and replaces part of the text with a different text using a regex expression |
| LOCATE | LOCATE(text, subtext, [start]) |
Returns the first occurrence of a the subtext in the text. If the subtext is not found, the function returns 0. Start is the starting position for the search. Position 1 is default. |
| EMPTY | EMPTY(value) | Checks if a value is empty or null |
|
STARTS_WITH ENDS-WITH |
STARTS_WITH(text, subtext) ENDS_WITH(text, subtext) |
Checks if a string starts with or ends with a certain substring |
| CURRENT_DATE | CURRENT_DATE() | Returns current date |
|
START_DATE END_DATE |
START_DATE() END_DATE() |
Return the start and end date of the dashboard date range |
| LENGTH | LENGTH(text) | Returns the length of a string |
| SPLIT | SPLIT(text, delimiter, index) | Returns the nth element of a string |
| TO_DATE | TO_DATE(value, format) | Converts string in the specified format to date data type |
| TO_TEXT | TO_TEXT(value[, format]) | Converts the value to text |
|
FIRST_DAY LAST_DAY |
FIRST_DAY(date) LAST_DAY(date) |
Returns the first/last day of the month based on the date provided |
|
DAY MONTH YEAR HOUR MINUTE |
DAY(date) MONTH(date) YEAR(date) HOUR(date) MINUTE(date) |
extracts (in number format) the day/month/year/hour/minute out of the date provided |
| IF | IF(logical_expression, value_if_true, value_if_false) |
Returns one value if logical expression is TRUE and another if FALSE |
| CASE | CASE(logical_expression) | Evaluates logical_expression to either TRUE or FALSE |
| THEN | THEN |
Execute the following when the IF/CASE statement is true |
| ELSE | ELSE |
Execute the following when the IF/CASE statement is false |
| ELSEIF | ELSEIF(logical_expression, value_if_true, value_if_false) |
Execute the following IF statement when the prior IF statement is false |
| END | END |
End the execution |
| WHEN | WHEN | Execute the following so long as the CASE statement is true |
| DAYS | DAYS(EndDate - StartDate) |
Calculates the number of days that are in the date range selected on screen in your date range selector. Leave the values exactly as "EndDate" and "StartDate" to leverage this dynamic formula. If you replace with actual dates, you will simply get the number of days between those two static dates. |