Common Formulas

Prerequisites

Accessing Formulas in Insights <2021.10

1. A user needs designer permissions and will need to navigate to the Insights Portal.

   

2. Then navigate to an existing dashboard or create a new dashboard.

   

3. Then edit a widget or create a new widget.

   

4. Then, the designer will have to select the plus next to the values

   

5. Select the fx button to create a new formula

   

6. Here, you will see the functions tab

   

7. Commonly used functions are highlighted for comparison in the Comparison table.

Accessing Formulas in Insights 2023.4

1. A user needs designer permissions and can navigate to an existing dashboard or create one from scratch.

   

2. Then, the user must edit the dashboard.

   

3. Then select add tile and then choose visualization.

   

   

4. Then select an explore.

   

5. Create a new custom formula using the Add New Measure, Add New Dimension, or Table Calculation option.

   

Comparison

Common Formulas	Insights <2021.10	Insights 2023.4
Sum- Adds the numbers in a list	In the functions tab, search for the Sum function and then select the function.	First select the Add button next to the custom fields. Select Table Calculation. Then type Sum and enter the necessary parameters. Choose a name for the table calculation and select Save. Check more details in the Looker user guide.
Round- Returns the rounded number	Search for the round function then select it from the tab.	First, select the Add button next to the custom fields. Select Table Calculation. Type Round and enter the necessary parameters. Choose a name for the table calculation and select Save. Check more details in the Looker user guide.
Now - Returns the current time period	Search for the Now function and then select it from the Functions tab.	First, select the Add button next to the custom fields. Select Table Calculation. Then type Now. Choose a name for the table calculation and select Save. Check more details in the Looker user guide.
Median - calculates the median	Search for the Median function in the Functions tab. Then select the function to bring the median function into the formula editor.	First, select the Add button next to the custom fields. Select Table Calculation. Then type Median and enter the necessary parameters. Choose a name for the table calculation and select Save. Check more details in the Looker user guide.
Max	Search for the Max function and select it to add it to the formula editor.	First, select the Add button next to the custom fields. Select Table Calculation. Then type Max and enter the necessary parameters. Choose a name for the table calculation and select Save. Check more details in the Looker user guide.
If - Executes a numeric expression based on a condition	In the Functions tab, search for if and then select the function.	First, click the Add button next to the custom fields. Select Table Calculation. Then type IF and enter the necessary parameters. Choose a name for the table calculation and select Save. Check more details in the Looker user guide.
Floor- Returns the number rounded down to the nearest multiple of “1”	Search for the Floor function and then select it to bring into the formula.	First, select the Add button next to the custom fields. Select Table Calculation. Then type Floor and enter the necessary parameters. Choose a name for the table calculation and select Save. Check more details in the Looker user guide.
Filter field in formula	(Field, filtered field) to filter a field inside a formula, add the field next to the original field, select it, select Rename, and then choose the filter.	To add a filter, select the Filter button next to the field name. Then you will see the filter at the top of the visualization.
Day Difference- Returns difference between start and end time	Search for day difference in the Functions tab then select the day difference function.	First, select the Add button next to the custom fields. Select Table Calculation. Then type diff_days and enter the necessary parameters. Choose a name for the table calculation and select Save. Check more details in the Looker user guide.
Count- counts number of unique values	Example: Count(ProcessName) In the Functions tab type Count and select the function. Then navigate to the Data Browser tab, and select processname inside of the function.	First, select the Add button next to the custom fields. Select Table Calculation. Type Count_Distinct and enter the necessary parameters. Choose a name for the table calculation and select Save. Optionally, select the three dots next to a field and select Count. Check more details in the Looker user guide.
Count All - returns actual item count of list of items, including duplicates	Example: DUPCount(ProcessName) In the Functions tab, search for Count All, and then select the statistical function.	First, select the Add button next to the custom fields. Select Table Calculation. Then select Type Count and enter the necessary parameters. Choose a name for the table calculation and select Save. Optionally, select the three dots next to a field and select Count. Check more details in the Looker documentation.
Case- Executes the result expression based on the condition. If no condition is met, the else condition is returned	Search for the Case function and then select it from the Functions tab.	First, select the Add button next to the custom fields. Select Table Calculation. Then type Case and enter the necessary parameters. Choose a name for the table calculation and select Save. Check more details in the Looker documentation.
Average- Calculates the mean	Search for Average in the Functions tab, then select it.	First, select the Add button next to the custom fields. Select Table Calculation. Type mean and enter the necessary parameters. Choose a name for the table calculation and select Save. Check more details in the Looker documentation.
ALL		Not available
Ceiling- Rounds up to 1’s digit	In the Functions tab, search for Ceiling, and then choose that function to bring into the formula editor.	First, select the Add button next to the custom fields. Select Table Calculation. Then type Ceiling and enter the necessary parameters. Choose a name for the table calculation and select Save. Check more details in the Looker documentation.