What’s in this article?
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 Active Insights 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.
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.
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 Widget using operators combined with free text.
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.
The SLICE function is ideal for nesting within a formula because it allows you to specify what values to include in the Widget 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, MEDIAN, MAX, 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.
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.
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.
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 Widget will display either the if true or if false parameter in your Widget. 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 Widget, while using a BLANK function as a parameter inserts blank values where the condition is not met.
These articles may help:
© 2004 – 2014 InsideSales.com
InsideSales.com technology is protected by the following United States Patents: 8078605, 8325738, 8352389, 8510382, 8566419.