Before you begin: This article is for TapClicks users who are familiar with Calculations and are interested in creating Advanced Calculations. For more information about Calculations in general, please refer to FAQs: Calculations or How to Create Calculations. Quickly navigate the article by using the table of contents below.
Advanced Calculation Function Reference
What Text Functions Are Available?
What are Common Use Cases for Text Functions?
What Date Functions Are Available?
How Do I Use the REMAINING_DAYS Function?
How Do I Create an Advanced Calculation To Handle Blank Values?
Advanced Calculation Function Reference
The following functions are available for creating Calculations in TapClicks. NOTE: Items in bold are required.
Name | Format | Description |
SUM | SUM(value1, [value2, …]) | The 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 the 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 uppercase |
LOWER | LOWER(text) | Converts text to lowercase |
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 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 a 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 the 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 in the date range selected on the screen in your date range selector. Leave the values exactly as "EndDate" and "StartDate" to leverage this dynamic formula. If you replace it with actual dates, you will get the number of days between those two static dates. |
Advanced Calculation Function FAQs
What Text Functions Are Available?
-
Presentation Functions:
- UPPER – Converts text to uppercase.
- LOWER – Converts text to lowercase.
- TRIM – Removes leading, trailing, and repeated spaces.
- CONCAT – Joins multiple text values together.
- GROUP_CONCAT – Aggregates values from multiple rows into a single string.
-
Naming Convention and Data Cleanup Functions:
- SUBSTRING – Extracts a portion of text.
- REPLACE – Replaces part of a text string with another value.
- REPLACE_REGEX – Uses regular expressions to replace specific text patterns.
- SPLIT – Separates text based on a specified delimiter.
- EXTRACT_REGEX – Extracts matching patterns from text using regular expressions.
- EMPTY – Checks if a field is empty or null.
-
Helper Functions:
- LOCATE – Finds the position of a substring within text.
- LENGTH – Returns the number of characters in a text string.
- STARTS_WITH – Checks if text starts with a specific substring.
- ENDS_WITH – Checks if text ends with a specific substring.
What are Common Use Cases for Text Functions?
Text functions can help with various data processing tasks, such as standardizing campaign names and identifiers to ensure uniform formatting, extracting key metrics or parameters from text strings for better analysis, and removing irrelevant or sensitive information by masking or deleting unnecessary details. They also assist in formatting data for clearer presentation and insights, making it more readable and actionable. Additionally, these functions automate data cleanup, ensuring consistency across datasets without requiring manual intervention.
What Date Functions Are Available?
-
Current and Relative Date Functions:
- CURRENT_DATE – Returns today’s date.
- REMAINING_DAYS – Returns the number of days left in the current month.
-
Start and End Date Calculations:
- START_DATE, END_DATE – Retrieves the start and end dates of a dataset.
- START_OF_DAY, END_OF_DAY – Returns timestamps for the beginning or end of a day.
-
Period Boundaries:
- FIRST_DAY – Returns the first day of the month.
- LAST_DAY – Returns the last day of the month.
-
Date Components:
- DAY, MONTH, YEAR, HOUR, MINUTE – Extracts specific parts of a date or timestamp.
-
Timeframe Adjustment:
- START_OF_DAY, END_OF_DAY – Helps standardize time ranges for reporting.
How Do I Use the REMAINING_DAYS Function?
The REMAINING_DAYS function calculates the number of days left in a given month based on a specified date.
For example:
- REMAINING_DAYS(Dec 12, 2025) = 19 (December has 31 days)
- REMAINING_DAYS(Sep 19, 2025) = 11 (September has 30 days)
This function is useful for pacing calculations, helping users track progress and determine if they are under-pacing or over-pacing their campaigns.
How Do I Create an Advanced Calculation To Handle Blank Values?
To replace empty values with a placeholder like "N/A", use the EMPTY function:
IF EMPTY(Field) THEN "N/A" ELSE Field END
This logic checks if a field is empty or null. If so, it replaces the blank with "N/A", ensuring cleaner and more informative outputs in reports.