Klipfolio offers a variety of Excel-like functions to perform calculations and manipulate data.
This article includes:
- The formula bar
- Removing column headers from raw data (for Klips users only)
- Learning about functions with our sample modelled data sources
- The Function Reference Guide
The formula bar
Functions are used in the formula bar in Klips (in the Klip Editor and when modelling a data source for use with custom Klips). They’re also used in PowerMetrics (when modelling data sources or editing data feeds for use with custom metrics).
In the formula bar, you can type in formulas or select from a list of functions. When you start typing a function name, a list of functions filtered by the letter you entered is displayed. Function Help with examples and parameters automatically displays for each function in a pop-up window.
There are some differences between writing formulas when modelling a data source/editing a data feed and writing formulas in the Klip Editor:
- When modelling a data source/editing a data feed:
- Calculations are performed to prepare and organize data and make it easier to use when building custom visualizations.
- Formulas reference tabular data by their sheet and column name, for example,
@A:A
or@Sheet 1, A:A
. - In the Klip Editor:
- Calculations are usually performed in the Klip Editor using modelled data sources. Using a data source that's already modelled means you can focus on writing effective formulas to customize your Klips, instead of spending time cleaning up and preparing your data.
- Formulas in the Klip Editor reference modelled data by their column name, for example,
@Product
or@Shipment location
. If a raw data source (one that's not modelled) is used in the Klip Editor, the syntax of the column reference is@A:A
.
Learn more about using the formula bar.
Learn more about modelled data sources.
Removing column headers from raw data (for Klips users only)
The column header is typically a textual title that gives each column of data a name. Since calculations and data detections are performed on entire columns, the column header can cause issues when it is in a different format than the rest of the column. For example, a column of data may be considered as text if it has a textual title even when it contains numeric data. For these reasons, it is important to remove column headers from your data. You can remove column headers in the following ways:
- Create a modelled data source from your raw data source and use the Exclude Rows property. Learn more about setting properties for a modelled data source.
- If you're in the Klip Editor, use the Filter action to Exclude the column header from your data. Learn more about the Filter action.
- In the formula bar, wrap the SLICE function around a column of data to remove the first row.
Note: When creating custom metrics and data sources using the query builder in PowerMetrics, column headers are automatically excluded from your data.
Learning about functions with our sample modelled data sources
The table below includes examples for almost all of the functions, by referring to one of the following modelled data sources: Example: Live Sales Data and Example: Product Data.
To add these modelled data sources to your account and follow along with the examples provided in the function reference guide table:
- Click Example: Live Sales Data.
- On the Configure a Data Source page, click Get data > Continue > Save.
- Optionally, name the data source.
- Click Save.
- Click Example: Product Data. and follow steps 2 - 4 again.
To access the sample modelled data sources, either click Data Sources in the left navigation sidebar or click your Account Name > Data Sources at the bottom of the left navigation sidebar. This opens your list of data sources where you can click on the data source name to go to its details page. Click Edit model to open the sample data in the modeller.
The Function Reference Guide
The reference guide table includes a categorized list of all the functions and their details. Klipfolio functions are separated into the following categories: Data Manipulation, Logic, Math, Text, Date/Time, and Statistics.
The purpose of each function is described along with information on how to use them. To help you find the function you're looking for, use CTRL+F or Command+F to search keywords like "align data".
Note: This list does not include details on the Klipfolio functions for JSON data. Learn more about Klipfolio JSON functions here.
Data Manipulation
Function |
Details |
ARRAY |
Use ARRAY to join together single values and return them as a single list of data in the order provided. Syntax and Parameters:
When to use this function: To combine data from multiple sources, such as data sources and results references. Example: Uses the Example: Live Sales Data data source
The result of this formula returns a single list as a combination of values in both columns.
In this example ARRAY is used to add the value, "Exception" to the list of data.
The result of this formula returns: Exception, Direct Billed, Customer, Distributor, Partner, Reseller |
BLANK |
Use BLANK to return one blank value or a specified number of blank values. Syntax and Parameters:
When to use this function: To replace a blank with a non-blank. Example:
The result of this formula returns 5 blank values.
Uses the Example: Live Sales Data data source In this example BLANK is used to replace the 0s in the Qty column with "No value"
The result of this formula returns the data in the Qty column with all blanks replaced with: No value. |
COUNTDISTINCT |
Use COUNTDISTINCT to count the number of unique items in each group. The results are returned in alphabetical order and aligned with the results of the GROUP function. Syntax and Parameters:
Related functions: When to use this function: To aggregate data according to specified groups. Example: Uses the Example: Live Sales Data data source In this example COUNTDISTINCT is used to count how many instances of US, Canada, and Mexico are in the Country column.
The result of this formula returns: 362, 87, 283. Note: You can also use the Group and Aggregate actions to return results similar to COUNTDISTINCT. Actions are available in the Klip Editor. Learn more about the actions menu. |
DATASOURCE |
Use the DATASOURCE function to indirectly refer to a data source field, by specifying the datasource ID (a unique 32-digit hexadecimal identifier) and a pointer to the field in the datasource. Syntax and Parameters:
When to use this function: To access a specific sheet name in an Excel/Google workbook where the sheet name is indicated by a variable or to access a specific XPath in a JSON/XML datasource that is inaccessible by XPath manipulation. Example: In this example, the values in column B of the data source specified is returned.
The result of this formula is the list of Products in column B. Note: The DATASOURCE function is used with raw data sources and not modelled data sources. Learn more: |
GROUP |
Use GROUP to group data into unique instances and hide duplicate values. The results are returned in alphabetical order. Syntax and Parameters:
Related functions: Example: Uses the Example: Live Sales Data data source In this example, all repeating values are grouped into one instance.
The result of this formula is Canada, Mexico, and US. Note: You can also use the Group action to return the same results as GROUP. Actions are available in the Klip Editor. Learn more about the actions menu.
|
GROUPBY |
Use GROUPBY to return values based on a specified aggregation method so that the unique values align with a parallel column. Syntax and Parameters:
Related functions: Example: Uses the Example: Live Sales Data data source In this example, the result of the formula sums the total number of days of activity per country.
The result of this formula returns: 5,270, 796, 3,895. The result is returned in the order the Country column is grouped in. Note: You can also use the Group action to return results similar to GROUP and GROUPBY. Actions are available in the Klip Editor. Learn more about the actions menu.
More examples: Uses the Example: Live Sales Data data source GROUPBY can be used to format your data as a comma separated list.
The result of this formula returns the days of activity associated to each Sales Rep (Ahmed Arthurs) in a comma separated list. The first ten values of the comma separated list for Ahmed Arthurs are: 5.0,13.0,14.0,17.0,14.0,30.0,12.0,23.0,10.0,6.0 Note: To see the comma separated list in your Klip, use the Properties panel to set the Data Format as Text. Learn more: |
FIRST |
Use FIRST to return the first values in a list of data where the number of values is specified by the count parameter. Syntax and Parameters:
Examples: Uses the Example: Live Sales Data data source
The result of this formula is the first value in the Company Name column: ADVANCE communications.
The result of this formula is the first five values of the Company Name column: ADVANCE communications, Nakamesh Import Consortium, Salamander Syndicate, Hahne-Kedar, Accelerant Investments. Note: If the value of the count parameter is greater than the number of items in the column, all items will be returned. If the data is grouped and there are fewer unique values than the value of the count parameter, only the number of unique values will be returned. |
LAST |
Use LAST to return the last values in a list of data where the number of values is specified by the count parameter. Syntax and Parameters:
Examples: Uses the Example: Live Sales Data data source
The result of this formula is the last value in the Company Name column: Hahne Kader.
The result of this formula is the last five values of the Company Name column: Janus, Ltd., Pacific Export Company, Bamberly Trust Corporation, Bamberly Trust Corporation, Hahne-Kedar Note: If the value of the count parameter is greater than the number of items in the column, all items will be returned. If the data is grouped and there are fewer unique values than the value of the count parameter, only the number of unique values will be returned. |
LOOKUP |
Use LOOKUP to correlate data between two data sources. For each input item, search for the first match in keys and return the value at the corresponding position in results. If there is no match, a blank is returned. Syntax and Parameters:
When to use this function: To correlate data between data sources and to align data across sub-components (for example, in a Bar/Line chart to align a series with the X-axis). Example: Uses the Example: Live Sales Data data source and Example: Product Data data source In this example, the first data source, Example: Live Sales Data is the primary data source and Example: Product Data is the secondary data source. Both data sources contain the same Product column. The intersecting Product column is used as a guide to align the data in the primary and secondary data sources.
The result of this formula returns the data in the Shipping Location column from the secondary data source aligned with the data in the Product column and additional columns in the primary data source. |
MAP/MAPFLAT |
Use MAPFLAT/MAP to repeat a formula or datasource reference (specified by the formula parameter) for each value in the values parameter. Syntax and Parameters:
When to use this function: To aggregate data over a set of account IDs by repeating a reference to a dynamic datasource where the account ID is passed in as a variable. Note: We recommend using MAPFLAT rather than MAP because MAPFLAT supports returning multiple items per first parameter while MAP returns only 1 item (if the formula returns >1 item, only the first item is returned). |
PADVALUES |
Use PADVALUES to return selected values with padding or additional values at the end of the data. Syntax and Parameters:
When to use this function: To make two lists of data the same size and to ensure data alignment. Example: Uses Example: Product Data data source
This formula adds "None" four times to the end of the Billing column.
More examples: In this example the difference between the number of values in column B and column C is used as the amount of values to pad column C with.
Note: This example uses a raw data source because modelled data source columns do not require padding. |
REPEAT |
Use REPEAT to replicate one or more values and return a list containing the number of copies specified. Syntax and Parameters:
Example:
The result of this formula is Canada, Canada, Canada
More examples: Uses the Example: Live Sales Data data source This example uses a Bar/Line chart. For the Series sub-component, the average Revenue value is repeated to create a target line. The number of times the average Revenue value is repeated is calculated using COUNT to count the amount of values on the X-Axis.
The result of this formula is a target line on your Bar/Line chart.
|
REPLACE |
Use REPLACE to replace items that match a specified value with another value. Syntax and Parameters:
Example: In this example, all values matching "US" in the Country column are replaced with "USA".
Uses the Example: Live Sales Data data source This example shows a nested replace where all values matching "US" in the Country column are replaced with "USA" and all values matching "Canada" in the Country column are replaced with "CA".
|
REVERSE |
Use REVERSE to reverse the order of a list of values. Syntax and Parameters:
Example: Uses the Example: Live Sales Data data source In this example, all the values in the Date column are returned in the reverse order.
In this example, SLICE specifies that REVERSE should only be applied to the last 12 items in the Date column.
|
SELECT |
Use SELECT to select values from a list according to specified criteria. Syntax and Parameters:
Example: Uses the Example: Live Sales Data data source This example returns the value from the Product column when the value in Customer Type contains "Distributor".
The first ten results of this formula are Bread, Bread, Eggs, Bread, Milk, Milk, Eggs, Milk, Butter, Butter. Learn more: |
SET |
Use SET to assign the values listed in the second parameter to the variables listed in the first parameter. Syntax and Parameters:
When to use this function: assign values for dynamic datasources that have multiple variables. Learn more: |
SLICE |
Use the SLICE function to return the subset of values between the start and end positions. If start and end parameters are not specified the first row is removed. Syntax and Parameters:
When to use this function: Often used to remove row headers from data. Example: Uses the Example: Live Sales Data data source In this example, the first row of data is removed from the Product column.
In this example, the subset of values between row 1 and 6 are returned. The result of this formula is Milk, Milk, Bread, Eggs, Eggs
Note: If start and end parameters contain negative values, values are returned from the end of the list.
|
SORT |
Use the SORT function to sort values according to the specified order. Syntax and Parameters:
Example: Uses the Example: Live Sales Data data source In this example, the Qty column is returned in descending numeric order.
The result of this formula returns the values in the Qty column descending from 15. Note: You can also use the Sort action to return results similar to SORT. Actions are available in the Klip Editor. Learn more about the actions menu. |
SPLICE |
Use the SPLICE function to return the values in the values parameter but delete the number of values specified in the count parameter from the position specified in the index parameter. Optionally inserts the value in the insert parameter at index
Syntax and Parameters:
Example: Uses the Example: Live Sales Data data source This example returns the values in the Sales Rep column with one value from position 5 (which is row 6, because row 1 is counted as 0), Tyrell Turek, removed and the value Mariam Rama added to the same position.
The first ten results of this formula are: Renaldo Recore, Wendell Wark, Jeffry Jarrett, Virgil Valenti, Ahmed Arthurs, Mariam Rama, Wendell Wark, Phil Phung, Virgil Valenti, Carmine Carman |
TRIM |
Use the TRIM function to remove the values specified in the pattern parameter or to remove all blank values if no pattern is provided. Syntax and Parameters:
Example: Uses Example: Product Data data source This example removes all blank values from the Shipping location column.
Uses the Example: Live Sales Data data source This example removes Milk from the Product column.
Note: The pattern parameter is case sensitive. |
Logic
Math
Text
Function |
Details |
CAPITALIZE |
Use CAPITALIZE to change the first letter in each word to uppercase. Syntax and Parameters:
Example: The result of this formula is Canada.
|
CONCAT |
Use the CONCAT function to join two or more values into one text string. Syntax and Parameters:
Example: Uses Example: Product Data data source In this example, " Units" is appended to every value in the Units column.
The first five results of this formula are 9 Units, 5 Units, 2 Units, 4 Units, 7 Units. More examples: Uses the Example: Live Sales Data data source In this example, the values in the Country column are appended to the main Wikipedia link. Using the Format as option in the Properties panel, the data is set to Hyperlink.
The result of this formula displays a link to each country's Wikipedia page.
Uses Example: Product Data data source In this example CONCAT is used to display the total number of units sold for 2018. The formula uses SUM to sum the total number of units from the Units column. NUMBERFORMAT is wrapped around the SUM function to ensure the numeric display of the sum is not using decimal places. CONCAT adds text to the display of the data.
This formula returns: Total for 2018: 3401 Units sold |
CONTAINS |
Use the CONTAINS function to test each value in the haystack parameter to see if it contains the value in the needle parameter. Syntax and Parameters:
Example: Uses the Example: Live Sales Data data source In this example, true is returned for all values in the Customer Type column that contain "Customer".
The first five results of this formula are false, false, true, false, true More Examples: Uses the Example: Live Sales Data data source In this example, the Price Base is returned for every instance of Mexico in the Country column.
The first five results of this formula are 3.04, 2.34, 3.04, 3.33, 1.42 |
COUNTRY_CLEAN |
Use the COUNTRY_CLEAN function to avoid multiple variations of a country’s name and return all the country names in the same form, either as full names or as ISO 3166 standardized names. If a value cannot be identified as a country, INVALID COUNTRY will display. Syntax and Parameters:
Examples:
The result is country names display in their full form, for example, Canada, Mexico, Canada, Canada, Canada, United States, United States, United States.
The result is country names display using the 2-character ISO code, for example, CA, MX, CA, CA, CA, US, US, US.
The result is country names display using the 3-character ISO code, for example, CAN, MEX, CAN, CAN, CAN, USA, USA, USA. |
INDEXOF |
Use INDEXOF to search the values in the text parameter for the specified occurrence of the search text (case sensitive) parameter and return the position of where it is found. If search text is not found at the specified occurrence, null is returned. Syntax and Parameters:
Example: Uses the Example: Live Sales Data data source This example returns the position of the second occurrence of "Car" (referring to the value Carmine Carman) in the Sales Rep column. INDEXOF(@Sales Rep, "Car",2)
The result of this formula returns 8 for every instance of Carmine Carman in the Sales Rep column. |
JOIN |
The JOIN function takes a set of values, joins these values with the glue parameter (a comma is used if glue is not specified), and returns them as a single value. Syntax and Parameters:
Example: Uses the Example: Live Sales Data data source This formula returns all values in the Product column as one, with * between each value. For example the beginning of the result looks like this: Bread*Milk*Milk*Bread*Eggs*Eggs*Milk*Butter |
LASTINDEXOF |
Use LASTINDEXOF to search the values in the text parameter for the last occurrence of the search text (case sensitive) parameter and return the position of where it is found. If search text is not found at the specified occurrence, null is returned. Syntax and Parameters:
Uses the Example: Live Sales Data data source This example returns the position of the second occurrence of "Car" (referring to the value Carmine Carman) in the Sales Rep column. LASTINDEXOF(@Sales Rep, "Car")
The result of this formula returns 8 for every instance of Carmine Carman in the Sales Rep column. |
LEFT |
Use the LEFT function to return the first character or a specified number of characters starting from the first value on the left. Syntax and Parameters:
Example: Uses the Example: Live Sales Data data source This formula returns the first letter of every value in the Product column.
The first ten results of the formula are B, M, M, B, E, E, M, B, B, B.
Uses the Example: Live Sales Data data source This formula returns the first 2 letters of every value in the Product column.
The first ten results of the formula are Br, Mi, Mi, Br, Eg, Eg, Mi, Bu, Bu, Br.
|
LENGTH |
Use the LENGTH function to count the number of characters in a text string. Syntax and Parameters:
Example: Uses the Example: Live Sales Data data source In this example, LENGTH is used to return the count of characters in each Company Name.
The first five results of the formula are 25, 11, 7, 24, 18. |
LOWER |
Use the LOWER function to change all characters in the values parameter to lowercase. Syntax and Parameters:
Example: Uses the Example: Live Sales Data data source
The first five results of the formula are bamberly trust corporation, janus, ltd., eclipse, taggert transcontinental, 5 icarus lines, inc. |
NUMBERFORMAT |
Use the NUMBERFORMAT function to take a set of values, treat them as numbers, and return them as text with as many digits of decimal places as specified by the precision parameter. Syntax and Parameters:
Example: Uses the Example: Live Sales Data data source
The first ten results of this formula return 4.8, 3.8, 3.1, 4.3, 4.6, 4.6, 3.8, 2.2, 2.4, 3.5 Note: If the data parameter is defined with text values or a blank, the results will be returned as a 0. |
REMOVE_EMOJI |
Emojis are pictographs most often seen in social media data sources, such as Facebook posts. They may also appear in other data source types. As new emojis are developed, new encoding is used and sometimes this encoding cannot be processed by Klipfolio. The REMOVE_EMOJI function is used to strip emojis from data so the remaining data can be processed. Syntax and Parameters:
Example: Where message and name point to the /data/message and /data/from/name fields in a Facebook data source.
Note: If the selected data does not contain emojis, the REMOVE_EMOJI function returns the selected data as is. |
RIGHT |
Use the RIGHT function to return the first character or a specified number of characters starting from the right of the first value. Syntax and Parameters:
Example: Uses the Example: Live Sales Data data source This formula returns the last letter of every value in the Product column.
The first ten results of the formula are d, k, k, d, s, s, k, r, r, d.
Uses the Example: Live Sales Data data source This formula returns the last 2 letters of every value in the Product column.
The first ten results of the formula are ad, lk, lk, ad, gs, gs, lk, er, er, ad. |
SUBSTITUTE |
The SUBSTITUTE function replaces a set of characters with another set of characters in a text string. If the occurrence parameter is specified, that occurrence is substituted, otherwise, all occurrences are substituted. Syntax and Parameters:
Example: Uses the Example: Live Sales Data data source This example SUBSTITUTES every instance of "-" with "/".
The first five results of this formula are: 2017/01/01 04:29:18 2017/01/01 10:26:56 2017/01/02 14:03:59 2017/01/02 17:35:23 2017/01/03 04:22:45
Uses the Example: Live Sales Data data source This example SUBSTITUTES only the second instance of "-" with "/".
The first five results of this formula are: 2017-01/01 04:29:182017-01/01 10:26:56 2017-01/02 14:03:59 2017-01/03 04:22:45 Note: While the SUBSTITUTE function is similar to the REPLACE function, the SUBSTITUTE function is used to replace part of a value. |
SUBSTITUTE_REGEX |
Use SUBSTITUTE_REGEX to substitute text based on a specific pattern, such as location in text.
REGEX (REGular EXPression) is a standard for describing patterns in text. There are several online resources, such as regexr.com, that describe how to define REGEX expressions.
Syntax and Parameters:
Example: The regex anchor, ^, is used to match only when 'a' is the first character in a text string.
The result of this formula is xpple, bread, eggs.
The result of this formula is xpple, bananx, orange.
|
SUBSTRING |
Use the SUBSTRING function to return a sub-string of text from a string of text. Syntax and Parameters:
Example: Uses the Example: Live Sales Data data source This formula returns the characters from the foutrth value to the end of the values in the Sales Rep column.
The first five results of this formula are ldo Recore, ell Wark, ry Jarrett, il Valenti, d Arthurs. |
TEXT_REVERSE |
Use the TEXT_REVERSE function to reverse the order of text or numbers in the data. Syntax and Parameters:
Example: Uses the Example: Live Sales Data data source This example returns the reverse of each value in the Product column.
The first five results of this formula are daerB, kliM, kliM, daerB, sggE.
|
TRIM_WHITESPACE |
Use the TRIM_WHITESPACE function to return the text without leading and trailing spaces. Syntax and Parameters:
The result of this formula returns: Reseller, Distributor, Customer without the leading or trailing spaces. |
TRUNCATE |
Truncates text to the specified number of characters, starting from the position parameter and optionally inserting a style character. Syntax and Parameters:
Example: This example cuts off the text string after 5 characters and adds an ellipsis to the end of the value.
The result of this formula is abcde... |
UPPER |
Use the UPPER function to change all characters in the values parameter to uppercase. Syntax and Parameters:
Example: The first five results of this formula are BAMBERLY TRUST CORPORATION, JANUS, LTD., ECLIPSE, TAGGERT TRANSCONTINENTAL, ICARUS LINES, INC.
|
URLDECODE |
The Klipfolio URLDECODE function decodes data that is encoded for use in a URL using URLENCODE, UTF-8, to plain text. Syntax and Parameters:
The result of this formula is https://www.klipfolio.com/klipfolio-certification. |
URLENCODE |
Use the URLENCODE function to encode data to be safely used in URLs. Syntax and Parameters:
Example:
The result of this formula is https%3A%2F%2Fwww.klipfolio.com%2Fklipfolio-certification. |
When using Date/Time functions in Klipfolio, it is important to remember that formulas process the dates in Unix time format. This means that human readable date formats have to be converted before they can be used in formulas. When converting dates, the format of the date (that you want to convert) must be specified and must be entered as a string surrounded by quotes in the formula. Learn more about Date/Time formats.
Note: You can use an external Unix time converter to find the Unix time format of readable dates.
Time zones are an optional parameter available for specification in Date/Time functions. However, time zones can also be set at your account level and do not typically need to be set individually for every formula. Learn more about time zones in Klipfolio.