Sample Beast Mode Calculations: Miscellaneous Transforms
Intro
Refer to this article to learn how to write a variety of miscellaneous Beast Mode calculations, such as calculating average time in seconds, removing NULL values, creating page Analyzer links, and so on.
Calculating Average Time in Seconds
Use the following code to determine the average time it takes for something. This will return the average time in seconds, but you can divide the output to get days, hours or minutes.
-- Days --
AVG(DATE(`end_date`)-DATE(`start_date`)) * 86400
+
-- Hours --
AVG(HOUR(`end_date`)-HOUR(`start_date`)) * 3600
+
-- Minutes --
AVG(MINUTE(`end_date`)-MINUTE(`start_date`)) * 60
+
-- Seconds --
AVG(SECOND(`end_date`)-SECOND(`start_date`))
Removing NULL for Line Graphs
Use the following code to create a calculation to filter NULL Values in Line graphs (so the line doesn't go to zero).
SUM(CASE WHEN `Number of SQLs` != 0 THEN`Number of SQLs` END)
SUM() is on the outside of the CASE statement as a best practice. If `Number of SQLs` = 0 it will return a NULL value.
Filtering Null Values
If you are trying to SUM the values in a column with NULL values, you need to convert those NULL values to zero using the IFNULL function.
For example,
IFNULL(`col`,0)
So let's say you had a "Category" column with values "A," "B," and "C," and an "Amount" column with values "20," "10" and an empty cell (which is NULL). When you SUM the Amount column, the IFNULL calculation converts the NULL value to 0 and then applies the SUM as normal.
Creating Page Analyzer Links (or Deep Linking with Filters)
Using a Beast Mode calculation, you can create links to other pages in Table cards and apply Analyzer filters according to what you have set in the Beast Mode. This is a powerful feature when you want to guide a user through a data story that involves cards that aren’t in the direct drill path of the current card, or if you want users to be able to apply Analyzer filters quickly to any page from a central or parent Table card.
To create one of these links, copy the following code into a Beast Mode calculation:
CONCAT('<a href="https://customer.domo.com/page/##########'
,'?pfilters=[{%22column%22:%22ColumnName%22,%22dataSourceId%22:%22DataSet ID%22,%22dataType%22:%22string%22,%22operand%22:%22OperandType%22,%22values%22:[%22'
,`Owner.Name`
,'%22]}]" target=%22_blank%22>'
,’Link Display Name’
,'</a>'
)
To create a custom Deep Link, replace the bold portions above with the following:
https://customer.domo.com/page/########## | Replace with the entire URL for the Domo page you want to navigate to when a user clicks on the link. |
ColumnName | Replace with the column name you want to filter on. |
DataSet ID |
Replace with the DatasetID of the DataSet you want to filter on. To locate this, in your Data Center, navigate to the DataSet. You'll find the ID in the URL.
Tip: To filter all cards on a page regardless of which DataSet powers it, remove the DataSet ID parameter from your Beast Mode altogether.
|
String | Replace with the data type of the column you're filtering on. This can be string, value, or date. |
OperandType | Replace with the type of operand you're filtering with. Options include IN or NOT_IN.
Tip: When using NOT_IN, the underscore must be included. Due to the operand being included in the URL, there cannot be a space.
|
Owner.Name |
Replace with the column that contains the values you want to filter on. For example, using Owner.Name values, when I click on the deep link in row one, data for Bob Smith will be returned. Choosing the link in row 3 will return data for Jeff Clark. |
target=%22_blank%22 | (Optional) Opens the link in a new tab in your browser. You can remove this portion from the Beast Mode if you prefer the link opened in the same browser tab. |
Link Display Name | The link display text. Instead of presenting an entire URL, you can specify how the link will be displayed. |
Adding Multiple Filters
To add more than one filter, copy the entire filter portion below:
,'?pfilters=[{%22column%22:%22ColumnName%22,%22dataSourceId%22:%DataSet ID%22,%22dataType%22:%22string%22,%22operand%22:%22OperandType%22,%22values%22:[%22'
,`Owner.Name2`
Insert the filter as many times as needed.
CONCAT('<a href="https://customer.domo.com/page/##########'
,'?pfilters=[{%22column%22:%22Owner.Name%22,%22dataSourceId%22:%DataSet ID%22,%22dataType%22:%22string%22,%22operand%22:%22OperandType%22,%22values%22:[%22'
,`Owner.Name`
,'?pfilters=[{%22column%22:%22Owner.Name%22,%22dataSourceId%22:%DataSet ID%22,%22dataType%22:%22string%22,%22operand%22:%22OperandType%22,%22values%22:[%22'
,`Owner.Name2`
,'%22]}]" target=%22_blank%22>'
,`Owner.Name`
,'</a>'
)
For more information on HTML in Domo, see Adding Graphics, Links, and Images to Table Cards Using Beast Mode.