What's in this article?
- Learn how to create a nested formula to build a Klip.
- Step-by-step instructions.
- Examples of useful nested formulas.
How to use nested formulas
You can create nested formulas that use multiple elements such as functions, expressions, and operators to manipulate your data. Nested formulas are an important part of Klipfolio Dashboard because they allow you to get exactly what you want out of your data. Nesting formulas can be as simple as using a SLICE function to clean up data before performing a math function, or as complex as nesting multiple functions within an ARRAY before formatting data in a series.
Using nested formulas
There are so many different ways you can use nested formulas that providing precise instructions is very difficult. Instead, this article will provide a general overview of creating a nested formula and some examples of useful formulas.
To create a nested formula
By using these formula editing tools, you can create nested formulas that are as complex or as simple as you want.
Expression – Allows you to add a set of parenthesis to tell the formula editor to evaluate this data first. Useful for nesting functions in complex equations, such as using a JOIN and ARRAY to create a comma separated list.
Free Text or Number – Allows you to manually enter text, number, or a function. This option is useful for inserting a value that is not available in your data source, such as a target value or dividing your values by a number.
Wrap with Function – Allows you to wrap selected elements in the formula field with a function of your choice. This option has a number of uses, such as nesting functions within each other to perform a complex calculation.
Wrap with Expression – Allows you to wrap selected elements in the formula field with a set of parenthesis. This is useful if you want to manipulate data you have assigned to the Klip using operators combined with free text.
Examples of nested formulas
The following examples of nested formulas use either the Wrap with Function or Wrap with Expression option. These examples are designed to show a few of the ways you can use a nested formula – refer to the relevant how to article to learn more about the specific functions in each formula.
SLICE function with a math function
The SLICE function is ideal for nesting within a formula because it allows you to specify what values to include in the Klip and also removes the first value, such as column header. This works well with any of the Math functions provided like a SUM, AVERAGE, MIN/MAX/MEDIAN, and other similar functions. In fact, the SLICE function can work in any number of nested formulas so long as you need to clean-up your data source prior to performing an equation.
ARRAY function with a math function
The ARRAY function is ideal for creating a nested formula because it formats data as a series of values in the order you have specified. By using either of the Wrap functions (see above), you can perform an equation on each value(s) in the series to create a column of data or to plot data points for a chart/graph. If you are using an entire column of data in your equation, consider using the SLICE function to clean that data before performing your equation.
ARRAY and JOIN functions to create a comma separated list
The ARRAY and JOIN functions can be used together to create a comma separated list of values for use in a spark line, spark bar, or win / loss chart. The JOIN returns a set of selected values as single value separated by a comma. For example, a column or row of values will be compressed into a single data point (12, 13, 14, 15). The ARRAY allows you to create a series of values in a specified order.
IF function with operators
The IF function is designed to perform a logic equation that returns a result for instances where the condition is true and where the condition is false. By wrapping the condition element of the IF function with an expression, you can create an equation that tests the values using an equation (eg, A:A>500). Based on the result of the equation, your Klip will display either the if true or if false parameter in your Klip. One interesting way you can use this type of equation is with wrapping the entire function in a TRIM and using a BLANK function for one of the parameters. The TRIM function removes all blank values from the Klip, while using a BLANK function as a parameter inserts blank values where the condition is not met.
Did you have trouble with the material in this article?
These articles may help: