Tips and Techniques - Function examples

The following are links to our Tips and Techniques function articles. These articles highlight some of the functions you can use to manipulate your data to build beautiful Klips inside Klipfolio.

This information is ideal for the new user getting starting with Klipfolio and for those looking for some direction on how to perform specific tasks.

All examples are based on Klips built for customers by our Support Team.

Function examples

In this section each function article highlights what you need to know about that function, gives a troubleshooting tip for working with this function, and builds a Klip that uses a simplified data set to reinforce how each function works. It is suggested that the articles be reviewed in the order presented so that you can better understand the formulas covered later on.

Functions - pdf download

Example Klip


The Formula Editing Toolbar - updated
- In this Klip example, get familiar with the Formula Editing Toolbar to build your first Klip. This Klip shows the top three fruit types as a percentage.

The SLICE function
- In this Klip example, use the SLICE function to remove the column header and to return the last value from a group of values.

The SELECT function
- In this Klip example, use the SELECT function to return values that meet a specific condition.

The GROUP and GROUPBY functions
In this Klip example, use the GROUP function to return each calendar month and the GROUPBY function to sum  related values.

The GROUPBY function - method
In this Klip example, use the GROUPBY method to join values and display the output as a Mini-Chart: Spark Bar. Other GROUPBY method options include count and average.

The DATE and DATEVALUE functions
In this Klip example, use the BETWEEN function with a number of different DATE functions to sum values in the last week.

The DATERANGE function - days
In this Klip example, use the DATERANGE function to return the days for the current week number.

The DATERANGE function - month and year
In this Klip example, use the DATERANGE function to return the month and year.

The LOOKUP function - updated
In this Klip example, use the LOOKUP function to create a map of The United States of America for visits in the last 30 days.

The LOOKUP function with variable
In this Klip example, use the LOOKUP function to align data where  gaps exist in the data set.  This Klip also uses a variable to make a date selection.

The LOOKUP function with SELECT
In this Klip example, use the LOOKUP function with the SELECT function to return a count of values that meets a specific condition.  This Klip also uses the DATERANGE function to display a set of dates.

The LOOKUP function with ARRAY
In this Klip example, use the LOOKUP function and the ARRAY function to display data from two different  data sources.

VARIABLES
In this Klip example, use variables to select your data in many different ways.

The COUNT and COUNTIF functions
In this Klip example, use different COUNT functions to return a single value for today, less than7 days, and greater than 7 days.
The COUNTDISTINCT and DATE_UNITVALUE functions
In this Klip example, use the DATE_UNITVALUE function to return the month and year and use the COUNTDISTINCT function to return a count of values.

The DATASOURCE function
In this Klip example, use the DATESOURCE function for data sources with the same data structure.

Working with multiple Google Analytics IDS
In this Klip example, use the MAP function with the DATASOURCE function to sum values by day and hour in a Sparkline component.

INDICATORS
In this Klip example, use indicators to display different colours depending on whether the date is older or less than 72 hours. In another Klip, use  an icon to let you know if a duplicate value exists.

DATE PICKER - one date
In this Klip example, use a date variable to select a single date to sum values.

DATE PICKER - two dates
In this Klip example, use two different variables to select a start and end date to sum values for different categories. Date selection is by month.

The SORT function - one column
In this Klip example, use one column to sort other columns in either descending or ascending order.

The SORT function - two columns 
In this Klip example, the SORT function lets you sort data by the primary and secondary columns. Also, HTML code lets you change the font size and color of column headers.

The DATE_ADD function
In this Klip example, use the DATE_ADD function to return data for the last seven days. Other options include second, minute,hour, day, week, month, quarter, or year.

DATE_IN function and Trend lines
In this Klip example, use the DATE_IN function to return dates in the current year. Other options include day, week, month, quarter, or year. This Klip also demonstrates trend lines.

The DATE_CONVERT function
In this Klip example, the DATE_CONVERT function lets you change the date format. Also, the X:Axis formula keeps the dates in chronological order.

The DATE_STARTOF function
In this Klip example, use the DATE_STARTOF function to change the starting date for the week from Sunday to Monday.

The IF function
In this Klip example, use the IF function to return the dates and values for the previous month. Use the TRIM function to remove the other dates.

The ARRAY function
In this Klip example, use the ARRAY function to type your own text and use two data sources to display values in a pie-chart.

OPERATOR - Average time/call
In this Klip example, use the division operator to calculate average time per call.

HTML Template
In this Klip example, use the HTML Template component to create a spacer Klip, a title header in a colored background , or to display text.

JOIN and MAP functions
In this Klip example, use the JOIN and MAP functions to join and display values in a Sparkline component.

SWITCH AND SET functions - updated
In this Klip example, use the SWITCH function to display monthly or yearly values. This Klip also demonstrates how to use the SET function to assign a formula to a text value for use in a Klip's overall formula.

Using Dashboard IDs
In this Klip example, locate a dashboard id and use it in a hyperlink to launch a URL. Create a link in a Klip to quickly navigate to another dashboard.

Click a hyperlink to reuse a Dashboard tab
In this Klip example, click a country to display related cities and values reusing the same Dashboard.

The AND/OR functions
In this Klip example, use the AND/OR functions to return a sales representative and manufacturer or all sales representatives and manufacturers.

The CUMULATIVE function
In this Klip example, use the CUMULATIVE function to return a sequential sum for a condition for the last seven days.

The MAP function using a JSON file type
In this Klip example, use the MAP function to return name and value fields in a JSON file type.

An example of a heat map
In this Klip example, use the HTML Template component to display earthquakes as heat spots.
Example source code
Use this source code to build the HTML component.

The CONTAINS and IN functions
In this Klip example, use two data sources to display a drop-down list of sites and related values. To select EBSite use the CONTAINS function.

The SUMIF function
In this Klip example, use the SUMIF function and the DATASOURCE function to sum yearly values for a specific vendor in a Values Pair component.

Create a map with a datepicker
In this Klip example, use the MAP function to display a sum of values after selecting a start and end date.

CONCAT and DATE_UNITVALUE functions
In this Klip example, the number of users for the last four quarters is displayed. The CONCAT function joins the quarters and year values together.

The Button component
In this Klip example, the button component is introduced. Also, the X:Axis uses a formula that helps with date sorting.

Dynamic data sources
Feature is still in beta
In this Klip example, a query uses a dynamic property to return multiple instances of data.

Create your own word cloud
In this Klip example, you are provided with the script code to create your own word cloud using the HTML component.

Use a Button variable to select dates in two different ways
In this Klip example, a button variable lets you select a specific date or a date selection in the last 30 days.

 Working with multiple Google Analytics IDs
In this Klip example, create a dynamic query referencing multiple Google Analytics ids to sum values for the same day and hour in a Sparkline component. This example makes use of the MAP function.

The PADVALUES function
In this Klip example, use the PADVALUES function to return values when one column ends with a blank cell.

Pie-Chart - Days Ago
In this Klip example, use the MAP function to build a pie-chart that uses a text field to sum values for a selected date range.

Mini-Chart: Win and Loss - For Advanced Users
In this Klip example, use the Win and Loss Mini Chart Format to display a different color depending on whether a date exists or not, in the last seven days.

Gauge - by Quarter and Month
In this Klip example, display current and target values by quarter or month. Use ranges for visual impact.

Value Pair - 30 day comparison
In this Klip example, display a percentage change between the totals for current month and previous month.

Sparkline - by date range
In this Klip example, learn how to display a value for today or values for the last seven days.

SUBSTRING & INDEXOF Functions
In this Klip example, learn how to display hour and minute values (displayed in your data source) by day.

FLOOR AND CEILING Functions
In this Klip example, bucket information into two different ranges: 0-25 and 26-50. Then add a Table to see what is in each bucket range.
Example source code
Use this source code to build these Klips.

Selecting data by row- NEW
In this Klip example, use the DATASOURCE function to select rows of data for display in a Klip.

Remember to keep reviewing our Tips and Techniques articles as you continue to explore how to use Klipfolio functions and components to build meaningful Klips with your own data set.

Related links:

Feedback and Knowledge Base