# Sample Beast Mode Calculations: Miscellaneous Transforms

Version 3

## 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.

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>'`

`)`

Note: When using pfilters in an iFrame, you cannot use single quote marks. Instead, use the ASCII code %22 to replace the quote marks.

To create a custom Deep Link, replace the bold portions above with the following:

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>'`

`)`

Note: When using pfilters in an iFrame, you cannot use single quote marks. Instead, use the ASCII code %22 to replace the quote marks.

## Taking Advantage of Webform Fields

Let's say that each month you need to update many cards to reflect the most recent closed month or current month in the filter. You want to streamline the filter as a reference point that can be updated once and trigger all the cards to use that same new month or year so each card does not have to be individually updated. For example: Closed Month = Sept or 9, Current Month = Oct or 10, Closed Year = 2016. You can do this by doing the following:

1. Create a webform with the date references, which you plan to manually update to specify the current month number and year as necessary.

2. Join the webform to your data using a DataFlow.

3. Create a Beast Mode calculation to take advantage of the webform fields.
For example, if you have a column called "Closed Month" that has a value of 9, this Beast Mode would work:

`CASE `

`WHEN MONTH(`Date`) = `Closed Month` THEN "Closed Month"`

`ELSE "Other"`

`END`

4. Use this Beast Mode in the filters. Set the filter to "Closed Month." Then, whenever you update the webform value, you will get new data displaying in all cards that use that filter.

Another option that won't require any manual intervention each month would be to use a DataFlow to calculate the MAX(`Date`) and add a column that identifies all data that is in the same month as the MAX(`Date`). Then filter on that field in the cards when you only want the card to use the most recent month.