The Ultimate Klipfolio Cheat Sheet

Behold! 'The Ultimate Klipfolio Cheat Sheet' for building metrics. ?

This is kind of like one of those cheat sheets you would make in school; a high level overview of the most important things you need to know in order to succeed. Print this out and keep it close by whenever you are working on your Klips. It should help make your building experience easier and more efficient.

The Klipfolio Cheat Sheet

Click here to download

Below you can find everything included in the cheat sheet, along with additional photos and a little more written context. This is a really big post so be sure to use the (CTRL/? + F) search function on your keyboard if you are looking for something specific. If you are new to Klipfolio and want to start things off with a bang, I recommend curling up next to a fire with a glass of wine and soft music and give this a full read.

Let's have at it, shall we?

'The Ultimate Cheat Sheet' covers:

1. The Klip Editor
2. Klip Components
3. Top Functions
4. Keyboard Shortcuts
5. Applied Actions
6. Sharing Dashboards and Klips

1. The Klip Editor

The Klip Editor is where you create and edit Klips. You can also create data sources in the Klip Editor.

Every Klip is made up of attributes called components that control virtually every part of your Klip, from its title to its visualization style. The Klip Editor lets you work with your Klip components to customize your Klip's data, properties, and appearance.

2. Klip Components

There are 18 different components available to use for building your Klips.

Presentation components

Before we begin, let’s take a minute to talk about the unsung, but often necessary, presentation components: the Layout Grid, Label, and Separator.

The Label component is ideal for creating section headers and adding descriptions to Klips. It can also be used to display a single value. Labels are versatile - they can include text, numbers, image URLs, hyperlinks, and mini charts.

Layout Grid is divided into cells by horizontal and vertical grid lines. You drag a Layout Grid into the Klip workspace and then insert components into its cells, for example, you could insert a table in one and an image in another. You can add and remove or adjust the width and height of each cell. For optimum Klip performance, avoid nesting a Layout Grid within another Layout Grid. If you want to increase the capacity of your Layout Grid, add rows and columns.components. These components provide structure and context for the components you add when building a Klip.

The Separator compnent is a line that’s used to create visual separation between components in a Klip. It can be positioned vertically or horizontally and helps to organize content, differentiate between components, control white space around components, and add visual impact to the overall appearance of a Klip.

Bar/Line Chart

The Bar/Line Chart component displays a comparison of values in either a bar or line chart form (or a combination of the two). It’s great for comparing metrics as well as showing trends over time.

This horizontal Bar Chart uses an inverted Bar Chart to show zero as a reference point between positive and negative numbers:

This stacked Bar Chart visually aggregates all of your categories into groups:

For a more detailed description of the Bar/Line component, see this article.

Table

The Table component displays information in columns and rows, so it’s ideal for showing tabular data, such as regional sales figures. It’s also a great way to display a lot of related data - an important factor in effectively utilizing dashboard space. Keep in mind, however, that clarity comes with simplicity - you don’t want to overload your table component with excessive information. The Table component’s versatility really shines through when you diverge from simple text and numbers and add things like mini-charts, links, results rows, and images. You can also enable drill-down functionality.

For a more detailed description of the Table component, see this article.

Value Pair

The Value Pair component includes a primary and secondary value and is ideal for monitoring a single KPI (primary value), such as a stock price or a ratio, alongside a label or comparison value (secondary value). You can also set indicator thresholds to show progress.

For a more detailed description of the Value Pair component, see this article.

Sparkline

The Sparkline component includes a line or bar graph that displays trends over time and/or the current value of a KPI. This component includes three chart types: Sparkline, Spark Bar, and Win/Loss.

For a more detailed description of the Sparkline component, see this article.

Pie Chart

The Pie Chart component displays categorical data divided into sections, so you can see each section’s value in comparison to the whole. It’s ideal for monitoring KPIs such as survey results or for a regional breakdown of revenue. Available as a pie or donut style, you can add information with tooltips and a legend, and click on each piece to see its unique data. You can also use colours and labels to add context and detail to your Pie Chart.

For a more detailed description of the Pie Chart component, see this article.

Scatter Chart

The Scatter Chart component shows a visual comparison of two sets of values in a chart. It’s best for data like survey results, test results, and demographics.

For a more detailed description of the Scatter Chart component, see this article.

Bubble Chart

The Scatter/Bubble Chart component allows you to show the correlation between two sets of data.

For a more detailed description of the Bubble Chart component, see this article.

Gauge

The Gauge component displays a value that’s relative to a predetermined target and is ideal for monitoring KPIs with set goals or benchmarks. They are best for tracking a single metric with a clear objective, for example, sales or revenue performance.

For a more detailed description of the Gauge component, see this article.

Map

The Map component enables you to display values for regions on a map. This component provides insight into regional and global data and performance. Its interactivity means you can hover over a region to see its associated value. Using map markers, you can specify locations by latitude and longitude. You can also create custom maps.

For a more detailed description of the Map component, see this article.

Image

The Image component displays an image that you specify by URL. This component works well for monitoring webcam outputs and is also a great way to add individual character, visual detail, and some flare to a Klip. You can use the Layout Grid to arrange multiple Image components in a single Klip.

For a more detailed description of the Image component, see this article.

Inline Frame

The Inline Frame component enables you to display content within an IFrame and is ideal for showing web content, such as widgets, flash, web pages, or videos.

For a more detailed description of the Inline Frame component, see this article.

User Input Control

The User Input Control component works with variables (that you create) and enables you to: search for values using a text field, select values to display from a drop-down list, or select a date range using a date picker. You can set its scope to apply to a Klip, a dashboard, or to all dashboards.

For a more detailed description of the User Input Control component, see this article.

Funnel Chart

The Funnel Chart component enables you to track your data as it moves through a process, for example, leads to sales and leads to sales conversions.You can use colour to highlight each stage in the process, include labels to add detail, and show conversions as a percentage from any stage in the process.

For a more detailed description of the Funnel component, see this article.

Button

The Button component is used to control the action of variables. For example, you can set a variable’s properties to toggle between dates for values when the button is clicked. The Button component can also be used to coordinate multiple User Input Control components, which is particularly useful when dealing with dynamic data sources.

For a more detailed description of the Button component, see this article.

Pictograph

The Pictograph component tells a story about your data using images or symbols, displaying data in a visual way. You can select from a library of images or upload your own.

For a more detailed description of the Pictograph component, see this article.

HTML Template

The HTML Template component enables you to create a custom Klip using any combination of HTML, Javascript, and CSS.

For a more detailed description of the HTML Template component, see this article.

News

The News component displays a stream of headlines from an online news source and is ideal for monitoring RSS feeds.

For a more detailed description of the News component, see this article.

All of the components listed above can be found in the Components Pallette in the Klip Editor.

The Components Pallette contains a list of all the components you can add to a Klip. Use the Components Palette to drag your chosen components onto a Blank Klip, or add components to any existing Klip. With the Components palette, you can create a Klip with multiple components.

Component Tree

Every Klip is made up of components - all components are shown in the tree.

3. Top Klipfolio Functions

Here is a full list of Klipfolio functions.

Klipfolio offers a variety of Excel-like functions to perform calculations and manipulate data. The purpose of these functions is to help you display your data accurately. Each function has a specific syntax and parameters that must be assigned values in order to perform calculations. You can nest functions within each other and use additional formulas to satisfy the parameter. There are examples of nested formulas throughout this article.

What are Klipfolio Functions?

Functions are used in the formula bar of the Modeller and the Klip Editor. The formula bar enables you to 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.

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: LOOKUP(input, keys, results)

input:A list of 1 or more items.

keys: A list of 1 or more items that intersects with the input list.

results: A list of 1 or more items from the same source as keys. Must contain the same number of items as keys.

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.

LOOKUP (@Product, @Product, @Shipping Location)

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.

DATE

Use DATE to convert dates specified in a given format into Unix time format.

Syntax and Parameters: DATE ( dates , format , [timezone] )

dates: A list of 1 or more date/time values.

format: The date format of the values in the dates parameter.

timezone: The time zone.

Example:

Uses the Example: Live Sales Data data source

DATE ( @Date, "yyyy-MM-dd HH:mm:ss" )

The result of this formula lists all the dates in the Date column in Unix time format. For example, the first five results return as 1483262958, 1483284416, 1483383839, 1483396523, 1483435365.

This example converts an entire date and time stamp to Unix time format.

DATE("2017-04-03T04:18:38.943Z","yyyy-MM-dd'T'HH:mm:ss")

The result of this formula is 1491207518.

MAPFLAT/MAP

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: MAPFLAT( values, variable name, formula)

values: A list of 1 or more values.

variable name: Name of the variable used in the formula parameter, typed as a literal string.

formula: Formula which uses variable name (entered as a $variable) and is executed for each item in values. If the formula returns more than 1 item, only the first value is returned.

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).

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: SUBSTITUTE ( text, old text, new text, [occurence] )

text: The values to be manipulated.

old text: The values that will be replaced.

new text: The values to replace the values in ;old text.

occurrence: [optional] Indicates the instance that will be replaced.

Note: While the SUBSTITUTE function is similar to the REPLACE function, the SUBSTITUTE function is used to replace part of a value.

ARRAY

Use ARRAY to join together single values and return them as a single list of data in the order provided.

Syntax and Parameters: ARRAY ( data )

data: The values to join together including strings or columns of data from different data sources.

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

ARRAY ( @Customer Contact, @Sales Rep )

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.

ARRAY ("Exception", @Customer Type)

The result of this formula returns: Exception, Direct Billed, Customer, Distributor, Partner, Reseller

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: CONTAINS ( haystack, needle )

haystack: A list of 1 or more items.

needle: The case-sensitive item to search for in the haystack parameter.

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".

CONTAINS(@Customer Type, "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.

SELECT(@Price Base, CONTAINS(@Country, "Mexico"))

The first five results of this formula are 3.04, 2.34, 3.04, 3.33, 1.42

GROUP

Use GROUP to group data into unique instances and hide duplicate values. The results are returned in alphabetical order.

Syntax and Parameters: GROUP ( values )

values: A list of 0 or more values

Example:

Uses the Example: Live Sales Data data source

In this example, all repeating values are grouped into one instance.

GROUP (@Country)

The result of this formula is US, Canada, and Mexico.

Note: You can also use the Group action to return results similar to GROUP. Actions are available in the Klip Editor. Learn more about the actions menu.

SELECT

Use SELECT to select values from a list according to specified criteria.

Syntax and Parameters: SELECT ( data, condition )

data: A list of 1 or more items.

condition: A list of true and false values, typically a formula which combines 1 or more Logic functions. Data and condition must have the same number of items.

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".

SELECT(@Product, @Customer Type="Distributor")

The first ten results of this formula are Bread, Bread, Eggs, Bread, Milk, Milk, Eggs, Milk, Butter, Butter.

COUNTIF

Use COUNTIF to test each value in a condition and count the true results of the condition.

Syntax and Parameters: COUNTIF ( condition )

condition: A list of true and false values.

Example:

Uses Example: Product Data data source

This example counts the number of shipments that are from the warehouse.

COUNTIF ( @Shipping location="warehouse" )

This formula returns: 30

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: SLICE ( values, [start] , [end])

values: A list of 0 or more items.

start: [optional] Indicates the number of items sliced off the top.

end: [optional] Indicates the position of the last item to be returned.

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.

SLICE ( @Product )

In this example, the subset of values between row 1 and 6 are returned.

SLICE( @Product, 1, 6 )

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.

DATE_CONVERT

Use the DATE_CONVERT function to convert values from one date format to another date format.

Syntax and Parameters: ;DATE_CONVERT ( values , format in , format out )

values: A list of 1 or more date/time values.

format in: The current date format of the date/time values.

format out: The date/time format you want to convert the dates to.

Example:

DATE_CONVERT("9/11/2018", "d/M/yyyy", "MMM dd, yyyy")

The result of this formula returns; Nov 09, 2018.

IF

Use IF to test a condition and specify the result of the condition if it evaluates to true or false.

Syntax and Parameters: IF ( condition , if true , if false )

condition: A list of 1 or more values to test.

if true: Data returned if the condition is true.

if false: Data returned if the condition is false.

Example:

Uses the Example: Live Sales Data data source

This example returns the value for the Company Name column if the corresponding value in the Country column is Canada. If the Country value is not Canada, the formula returns a blank value.

IF(@Country="Canada",@Company Name, BLANK())

More examples:

Uses the Example: Live Sales Data data source

In this example IF is used to establish a true and false condition. AND is used to create a statement with multiple conditions. This statement is saying if the dates from the Date column fall in the last year and the Customer Type column lists that date as having a customer that is a Direct Customer, then return the Revenue value. If these conditions are not met, return 0.

IF(AND(DATE_IN(DATE(@Date, "yyyy-MM-dd HH:mm:ss"),year,-1),@Customer Type="Direct Customer"),@Revenue, 0)

The first ten results of this formula are 0, 0, 27.81, 0, 18.32, 0, 0, 10.85, 0, 0.

Note: It is strongly recommended to only nest up to two IF formulas. If more conditions are needed, use SWITCH or SELECT instead.

GROUPBY

Use GROUP to group data into unique instances and hide duplicate values. The results are returned in alphabetical order.

Syntax and Parameters: GROUP ( values )

values: A list of 0 or more values

Example:

Uses the Example: Live Sales Data data source

In this example, all repeating values are grouped into one instance.

GROUP (@Country)

The result of this formula is US, Canada, and Mexico.

Note: You can also use the Group action to return results similar to GROUP. Actions are available in the Klip Editor. Learn more about the actions menu.

SWITCH

Use SWITCH to switch a value to another value based on whether the case is evaluated to be true. If no match is found null is returned.

Syntax and Parameters: SWITCH ( data, case, values )

data: A list of 1 or more values.

case: The condition to be evaluated as either true or false.

values: The value to be returned if the case is true.

CONCAT

Use the CONCAT function to join two or more values into one text string.

Syntax and Parameters: CONCAT ( data )

data: The value or values to join together.

Example:

Uses Example: Product Data data source

In this example, " Units" is appended to every value in the Units column.

CONCAT(@Units, " Units")

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.

CONCAT("https://en.wikipedia.org/wiki/", @Country)

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.

CONCAT( "Total for 2018: ", NUMBERFORMAT(SUM(@Units))," Units sold" )

This formula returns: Total for 2018: 3401 Units sold

COUNT

Use COUNT to return a count of all non-blank (numeric and text) items in data.

Syntax and Parameters: COUNT ( data )

data: A list of 1 or more items.

Example:

Uses Example: Product Data data source

In this example the number of shipments is calculated by the COUNT of the values in the Shipping location column of data.

COUNT ( @Shipping location )

This formula returns: 754

BETWEEN

Use BETWEEN to return true or false if a value is numerically between a (inclusive) start and end.

Syntax and Parameters: BETWEEN ( values, start, end  )

values: A list of 1 or more numeric items.

start: Numeric start of range.

end: Numeric end of range.

When to use this function: To determine if a list of dates falls within a specific date range (all dates must be in Unix time format).

SORT

Use the SORT function to sort values according to the specified order.

Syntax and Parameters: SORT ( values, type, [return values])

values: A list of 1 or more values.

type: The sort order. Choose between: ascending, ascending numeric, descending, or descending numeric.

return values: [optional] A list of 1 or more values that corresponds to the values parameter.

Example:

Uses the Example: Live Sales Data data source

In this example, the Qty column is returned in descending numeric order.

SORT ( @Qty, descending numeric )

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.

TIME

Use the TIME function to convert a date/time duration, specified as a combination of days, hours, minutes and seconds, to number of seconds.

Syntax and Parameters: TIME ( values, format )

values: A list of 1 or more date/time durations.

format: The format of the date/time duration.

Supported formats include:

• hh:mm:ss
• hh:mm
• dd:hh:mm:ss
• dd:hh:mm
• dd:hh
• d
• h
• m
• s
• ss
• dd
• mm
• hh
• ss
• mmss
• ddhhmmss

4. Popular Keyboard Shortcuts

5. Applied Actions

Filter, Group, Aggregate, Hide, and Sort on visualizations without the use of formulas on all components.

To see if any actions have been applied to your visualization, click the asterisk icon (*) next to the visualization type name. A list displays showing applied actions. You can delete actions applied to your visualization from this list.

Filter

To apply a Filter, click the menu next to the highlighted row in the component tree and select Filter or right-click the series in a chart or a column in a table. You can also click Add Filter in the Properties panel. Learn more about the Filter action.

When the Filter action is selected from the More Actions menu, a Filter dialog appears. The result of the applied filter can be seen in the Klip visualizer.

The type of filter available is based on the data and component. For example, text is filtered with a pick list, while numbers are filtered with a slider or input box. User Input Control components always use a pick list.

When a filter is applied, a hint is displayed next to the series or column row in the component tree.

Watch this video for an example of how to Filter on Hidden Data:

Group and Aggregate

After applying a Group, Series data can be aggregated with standard aggregations including Sum, Average, and Count Distinct. Group and Aggregation settings are available from the component tree and the Properties panel.

Group

To apply a Group, right-click the sub-component, click the menu next to it in the component tree or check Group repeating labels in the Properties panel.

Aggregate

To apply an Aggregation, click the menu next to the sub-component in the component tree and select Aggregation or right-click the data in the component visualization to access the More Actions menu. You can also use the Aggregation menu in the Properties panel.

Sort

To apply a Sort, click the menu next to the sub-component in the component tree and select Sort or right-click the data in a component. You can also use the Sort menu on the Properties panel.

Note: You can apply a Sort from the More Actions menu or use the SORT function. Only one Sort (the most recent) is supported at a time. The Sort action is always applied last.

When a sort is applied, a hint is displayed next to the series or column row in the component tree (as shown below).

Watch this video for an example of how to Sort on Hidden Data:

Hidden Data

You can use actions on Hidden Data (Additional Data) allowing you to modify your visualization with added Data sub-components. The actions applied to these additional sub-components change your visualization but do not add Series, Axes, Columns, or other elements to your component.

Add Hidden Data from the Properties panel or Control panel of the Klip Editor:

Watch this video for an example of how Hidden Data is used with Indicators:

6. Sharing

Sharing your data with others encourages collaboration and discussion. It also keeps everyone aware of the current situation and focussed on common goals. Klipfolio offers many ways to share, providing the flexibility to share internally, externally, in real time, or periodically.

Before sharing, add users and groups

In order to share within your organization, you first need to add users and, optionally, organize them into groups. Organizing individuals into relevant groups can save you a lot of time when sharing. For example, if you have a dashboard you can’t wait to share with your Support Team, if you’ve already created the Support Team group and added all its members, you can share with everyone at the same time!

Note: To simplify cross-company sharing, all users in your account are automatically added to All Users. When users are added or removed from your account, the "All Users" list is automatically updated.

Learn more:

• Adding users
• Creating groups

Ways to share

User and group sharing: Enable viewing or editing access to dashboards, Klips, data sources, metrics, and Metric Boards with individuals and groups in your organization.

• Sharing dashboards
• Sharing Klips
• Sharing data sources
• Sharing metrics and Metric Boards
• Sharing FAQ

Published links: Share internally or externally via a link to your dashboard.

Choose to make your dashboard publicly available, searchable, and indexable by search engines, or restrict access to those with a link and, optionally, a password.

Learn more about published links

Automated snapshots: Set up scheduled emails to share snapshots of individual Klips or entire dashboards. You decide who receives the snapshot, at what time, and how often.

Learn more about automated emails

PDF reports: Klips and dashboards can be downloaded as images or as PDFs (great for presentations and printed reports) and shared as email attachments

Best practices for generating PDF snapshots

Display your dashboards on office wallboards: Keep everyone up to date by displaying one dashboard or setting up multiple dashboards to display in rotation.

Using wallboards to highlight your company's data is a great way to keep your teams data-driven and focussed on performance.

Displaying dashboards on a large screen

Share your Klips and dashboards with Slack: Once our Slack integration is enabled in your account settings, sharing individual Klips or dashboards with users or channels in Slack is fast and easy.

Sharing Klips and dashboards with Slack

Embed Klips and dashboards on your website or private intranet: You can seamlessly embed individual Klips or entire dashboards into your website or host application.

Embedding Klips

Add your comments to a Klip: Anyone with access to a Klip can write a comment. Adding comments encourages collaboration and is a great way to share your insights with others.

Commenting on Klips

This is the end, my friend!

If you were able to read this entire thing front to back and made it this far you deserve a reward.

Hopefully, this resource has been helpful. Happy Klip building!