Create or Edit a Function

Upland Analytics allows you to define your own powerful formulas to meet your specific reporting requirements. If there is a need to perform some calculations or to add specialized data in the report, you can use functions.

A number of options are available to you when choosing the Function Type. This topic details each of the available Conditions with examples.

Average

The Average condition returns the average of the measure selected within the Apply condition over option. Average is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5.

Note: When Average Condition is chosen, Format and Apply condition over options are enabled.

Create an Average Function

  1. Click Create New on the Custom Fields tab.
  2. Click the Type drop-down arrow and select Function.
  3. Click the Condition drop-down arrow and select Average from the available options.
  4. Click the Format drop-down arrow and select one of the following:
    • Integer
    • Decimal
    • Money
  5. Click the Apply condition over drop-down arrow and select the measure over which you want to apply the Condition.
  6. In the Custom Field Name box, type the name for the new custom field.
  7. Click Add Where Clause to apply a WHERE clause. For more information, see Add a Where Clause Function Custom Field.
  8. Click one of the following:
    • Cancel to return the previous page.
    • Save & New to save the custom field and create a new one.
    • Save & Continue to save the custom field and continue working on it. This is useful when working on a large formula or function.
    • Save & Close to save the custom field and then close the Custom Field Builder. You will be redirected to the Report Editor screen where the newly created custom field(s) can be used in a report or graph/chart.

Count

The Count condition returns the number of the entries within the Apply condition over field. For example, the count of 2, 3, 3, 5, 7, and 10 is 6.

Note: When Count Condition is chosen, Apply condition over option is enabled.

Create a Count Function

  1. Click Create New on the Custom Fields tab.
  2. Click the Type drop-down arrow and select Function.
  3. Click the Condition drop-down arrow and select Count from the available options.
  4. Click the Apply condition over drop-down arrow and select the measure over which you want to apply the Condition.
  5. In the Custom Field Name box, type the name for the new custom field.
  6. Click Add Where Clause to apply a WHERE clause. For more information, see Add a Where Clause Function Custom Field.
  7. Click one of the following:
    • Cancel to return the previous page.
    • Save & New to save the custom field and create a new one.
    • Save & Continue to save the custom field and continue working on it. This is useful when working on a large formula or function.
    • Save & Close to save the custom field and then close the Custom Field Builder. You will be redirected to the Report Editor screen where the newly created custom field(s) can be used in a report or graph/chart.

Count Distinct

The Count Distinct condition returns the distinct number of the entries within the Apply condition over Field. For example, the count distinct of 2, 3, 3, 5, 7, and 10 is 5.

Note: When Count Distinct Condition is chosen, Format and Apply condition over options are enabled.

Create a Count Distinct

  1. Click Create New on the Custom Fields tab.
  2. Click the Type drop-down arrow and select Function.
  3. Click the Condition drop-down arrow and select Count Distinct.
  4. Click the Apply condition over drop-down arrow and select the measure over which you want to apply the Condition.
  5. In the Custom Field Name box, type the name for the new custom field.
  6. Click Add Where Clause to apply a WHERE clause. For more information, see Add a Where Clause Function Custom Field.
  7. Click one of the following:
    • Cancel to return the previous page.
    • Save & New to save the custom field and create a new one.
    • Save & Continue to save the custom field and continue working on it. This is useful when working on a large formula or function.
    • Save & Close to save the custom field and then close the Custom Field Builder. You will be redirected to the Report Editor screen where the newly created custom field(s) can be used in a report or graph/chart.

If

The IF THEN ELSE condition is a useful tool for creating a variety of calculated elements by verifying whether a condition is met or not. If the condition is met, it returns true. IF THEN ELSE can be used in calculations in several different ways, including filtering, grouping, bucketing, and relabeling results. When using IF THEN ELSE, you will need to provide an element or condition to test and values if the expression passes or fails.

Note: When If Condition is chosen, the Format option is enabled and the Function editor is available.

Create an If Function

  1. Click Create New on the Custom Fields tab.
  2. Click the Type drop-down arrow and select Function.
  3. Click the Condition drop-down arrow and select If.
  4. In the Custom Field Name box, type the name for the new custom field.
  5. Click the Format drop-down arrow and select from the following options:
    • Integer
    • Decimal
    • Money
  6. In the Function Editor box, do the following:
    1. Click the Select an option drop-down arrow and select a field from the available list.
    2. Select one of the following operators: equal, not equal, in, not in, less, less or equal, greater, greater or equal, between, not between, is null, or is not null.
    3. Type the condition value.
  7. Click Add Filter. The AND and OR buttons will be enabled. Perform steps 6-7 as many times as necessary.

    8. Note: To add a nested If condition, see Nested If Condition.

  8. In the When true box, do the following:
    1. Start typing the name of the expression you would like to use.
    2. Select the available expression from list.
    3. Note that the selected expression is added to the When true box.
  9. In the When false box, do the following:
    1. Type the name of the expression.
    2. Select the available expression from list.
    3. Note that the selected expression is added to the When false box.
  10. Click one of the following:
    • Cancel to return the previous page.
    • Save & New to save the custom field and create a new one.
    • Save & Continue to save the custom field and continue working on it. This is useful when working on a large formula or function.
    • Save & Close to save the custom field and then close the Custom Field Builder. You will be redirected to the Report Editor screen where the newly created custom field(s) can be used in a report or graph/chart.

Note: Clicking Delete will delete the line item it's associated to.

Nested If Condition

You can add an IF...THEN (or IF...ELSE...THEN) statement inside another IF...THEN statement. Nesting multiple conditional IF THEN ELSE statements can be useful for creating several different groups or filtering by different conditional expressions.

There are no limits to the number of IF...THEN statements you could create and you can get as complicated as you like.

Create a Nested If Function

  1. Click Create New on the Custom Fields tab.
  2. In the Custom Field Name box, type the name for the new custom field.
  3. Click the Type drop-down arrow and select Function.
  4. Click the Condition drop-down arrow and select If.
  5. Click the Format drop-down arrow and select one of the following:
    • Integer
    • Decimal
    • Money
  6. In the Function Editor box, do the following:
    1. Click the Select an option drop-down arrow and select a field from the available list.
    2. Select one of the following operators: equal, not equal,in, not in, less, less or equal, greater, greater or equal, between, not between, is null, or is not null.
    3. Type the condition value.
  7. Click Add group. The AND and OR buttons are enabled. Perform steps 6-7 as many times as necessary.
  8. In the When true box, do the following:
    1. Start typing the name of the expression you would like to use.
    2. Select the available expression from list.
    3. Note that the selected expression is added to the When true box.
  9. In the When false box, do the following:
    1. Start typing the name of the expression you would like to use.
    2. Select the available expression from list.
    3. Note that the selected expression is added to the When false box.
  10. Click one of the following:
    • Cancel to return the previous page.
    • Save & New to save the custom field and create a new one.
    • Save & Continue to save the custom field and continue working on it. This is useful when working on a large formula or function.
    • Save & Close to save the custom field and then close the Custom Field Builder. You will be redirected to the Report Editor screen where the newly created custom field(s) can be used in a report or graph/chart.

Note: Clicking Delete will delete the line item it's associated to.

Maximum

The Maximum condition is used to return the highest value of the Measure selected within the Apply condition over option. For example, the maximum of 2, 3, 3, 5, 7, and 10 is 10.

Note: When Maximum Condition is chosen, Format and Apply condition over options are enabled.

Create a Maximum Function

  1. Click Create New on the Custom Fields tab.
  2. Click the Type drop-down arrow and select Function.
  3. Click the Condition drop-down arrow and select Maximum.
  4. Click the Format drop-down arrow and select one of the following:
    • Integer
    • Decimal
    • Money
  5. Click the Apply condition over drop-down arrow and select the measure over which you want to apply the Condition.
  6. In the Custom Field Name box, type the name for the new custom field.
  7. Click Add Where Clause to apply a WHERE clause. For more information, see Add a Where Clause Function Custom Field.
  8. Click one of the following:
    • Cancel to return the previous page.
    • Save & New to save the custom field and create a new one.
    • Save & Continue to save the custom field and continue working on it. This is useful when working on a large formula or function.
    • Save & Close to save the custom field and then close the Custom Field Builder. You will be redirected to the Report Editor screen where the newly created custom field(s) can be used in a report or graph/chart.

Note: Clicking save will add the newly created custom field to the Custom Fields tab.

Minimum

The Minimum condition is used to return the lowest value of the Measure selected within the Apply condition over option. For example, the minimum of 2, 3, 3, 5, 7, and 10 is 2.

Create a Minimum Function

  1. Click Create New on the Custom Fields tab.
  2. Click the Type from the drop-down arrow and select Function.
  3. Click the Condition from the drop-down arrow and select Minimum.
  4. Click the Format from the drop-down arrow and select one of the following:
    • Integer
    • Decimal
    • Money
  5. Click the Apply condition over from the drop-down arrow and select the measure over which you want to apply the Condition.
  6. In the Custom Field Name box, type the name for the new custom field.
  7. Click Add Where Clause to apply a WHERE clause. For more information, see Add a Where Clause Function Custom Field.
  8. Click one of the following:
    • Cancel to return the previous page.
    • Save & New to save the custom field and create a new one.
    • Save & Continue to save the custom field and continue working on it. This is useful when working on a large formula or function.
    • Save & Close to save the custom fieldand then close the Custom Field Builder. You will be redirected to the Report Editor screen where the newly created custom field(s) can be used in a report or graph/chart.