Create reusable custom functions

You can define custom functions to represent frequently used complex calculations that combine logic, aggregates, and other type of operations. There are many advantages to adding custom functions to your Sigma practice:

• Use custom functions to encode business logic instead of repeating it.
• Encapsulate complex calculations that are common business use cases, for easier use.
• Expose your proprietary warehouse functions, making them consumable.

Requirements

You must have Admin privileges to create and manage custom functions. Anyone can use custom function in the same manner as built-in Sigma functions.

Limitations

• You can use custom functions inside workbooks and metrics.
• All arguments of the custom function are required; there are no optional arguments.
• User-defined functions (UDFs) from your data warehouse only work for that specific warehouse and schema; there is no cross-warehouse support.

Create custom functions

The following steps describe how to create a custom function:

1. Navigate to Administration.

2. Select Account on the left navigation tab.

3. In the Account section, scroll to the bottom of the page.

4. Under the Custom functions heading, you can see all custom functions that you previously defined on your account.

5. In the Create custom function area, click Add.
   

6. In the Add new custom function page, specify the new function.

   

   Name

   Required.

   Name your function.

   Valid function names must adhere to these rules:

   • Contain only letters and numbers
   • Begin with a capital letter
   • Unique (case-insensitive) compared to all other custom and built-in functions
   • Maximum 128 characters long

   Description

   Optional.

   Describe what your function does.
   If your organization habitually uses multiple data warehouses, and you are using one or more UDFs, specify the warehouse and schema of the UDF. See SplineModel example.

   Arguments

   Optional.

   Iteratively add arguments to your function by clicking + Add argument for each argument.

   To delete an argument, click the x (remove/delete) icon next to its description.

   For each argument, specify the following:

   Name

   Required.

   Name of the argument.

   Valid argument names must adhere to these rules:

   • Contain only letters, numbers, spaces, and underscores
   • Cannot begin with a space
   • Maximum 128 characters long

   The default arguments are arg1, arg 2, and so on. Rename the arguments to accurately represent the input data.

   Type

   Required.
   The data type of the argument.
   The default data type is Number. You can switch to Text, Logical, Datetime, Variant, or Geography data type, depending on the input data.

   Description

   Optional.

   Describe the data that the argument represents.

   Formula

   Required.

   Use built-in functions, operations, and the arguments you defined to build the formula for your custom function.

   Return type

   The data type of the return value.

   Sigma populates this field automatically, based on the formula and the arguments.

   Include function
   in formula bar
   suggestions

   Optional.
   Turn the switch to the on position after you tested and finalized the new custom function.
   This makes the new function appear as a suggestion on the formula bar in relevant workbooks.
   Note that you can use a hidden custom functions by name, and hiding a function does not break existing formulas and elements that consume this custom function.

   Click **Save**

1. After saving the new custom function, it appears in the list of custom functions.
   
2. Depending on your choice for the Include function in formula bar suggestions switch, this custom function has the status of Visible or Hidden.

After you create custom functions, you can use them in the workbook's formula interface exactly in the same way as you would use Sigma's built-in functions. The only difference is that Sigma's functions typically include a usage example.

Note that these functions have the Custom label.

Edit custom functions

To change an existing custom function, follow these steps:

1. Navigate to the Custom functions page; User > Administration > Account > Custom functions.
2. For the function you plan to edit, click the ellipsis icon.
   
3. Select Edit.
   
4. In the Update custom function modal, make the necessary changes, and then click Save.
   To delete an argument, click the x (remove/delete) icon next to its description.
   Note that if you are changing the signature of the function, such as its name, the number and type of arguments, or the formula, the elements that use the function may break.
   
5. The updated custom function appears in your list of custom functions.
   

Hide custom functions

Hiding is equivalent to keeping the function name from appearing as a suggestion in the formula interface. It does not remove the function from your Sigma instance or account, or break the elements that use this function.

We recommend that you hide functions as a step in deprecating them, effectively limiting and preventing their use in new solutions.

To hide a custom function, follow these steps.

You can also hide the function when using the Add custom function and Update custom function interfaces.

1. Navigate to the Custom functions page; User > Administration > Account > Custom functions.
2. For the function you plan to hide, click the ellipsis icon.
3. Select Hide function.
   
4. In the list of custom functions, Sigma changes the function status to Hidden.
   

Delete custom functions

Deleting a custom function removes it from your Sigma instance or account, and breaks the elements that use this function.

To delete the custom function, follow these steps.

1. Navigate to the Custom functions page; User > Administration > Account > Custom functions.
2. For the function you plan to delete, click the ellipsis icon.
3. Select Delete.
   
4. The function no longer appears in the list of custom functions.

Examples of custom functions

Slice: a custom function for Text

This example demonstrates how to use a custom function Slice to extract a portion of text. It uses the built-in Sigma function Substring.

Name

Slice

Description

Get a slice of a text string, at the specified start and end points.

Arguments

text

Text data type

The Text we plan to slice.

start

Number data type

The starting index of the slice

end

Number data type

The ending index of the slice

Formula

Substring([text], [start], [end] - [start] + 1)

Return type

Text

SplineModel: wrap complex syntax of custom warehouse functions

When you want to access a UDF from your data warehouse, we recommend that you use a custom function as a wrapper, to ensure that the calling syntax is correct and all necessary definitions migrate into Sigma.

We also recommend that you identify the warehouse and relevant schema in the description of the function, if you use multiple data stores. This is a reminder for the users that the function is specific to the connection that references the named UDF. 

In this example, we use the built-in passthrough Sigma function CallVariant to ensure we get the correct data type from the original warehouse function. 

Name

SpliceModel

Description

Snowflake: Apply the spline model to the input.
Note that we identified the warehouse where the UDF is defined.

Arguments

arg1

Number data type

First argument

arg2

Number data type

Second argument

Formula

CallVariant("PRODUCT.SIGMA.SPLINEMODEL", ArrayAgg([arg1]), ArrayAgg([arg2]))

Return type

Variant