> ## Documentation Index
> Fetch the complete documentation index at: https://domoinc-openapi-sync-dataflows.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Troubleshooting Beast Mode Calculations

### Intro

This article describes how to troubleshoot common errors when using Beast Mode calculations, including case statement formatting, date formatting, and others.

***

### Case Statement Formatting

A case statement requires a few keywords placed in a particular order.

```
    CASE
    WHEN `State` = 'California'
    THEN 'CA'
    ELSE `State`
    END
```

The case statement will be in error if it doesn’t include these elements or is not written correctly.

You can include as many `WHEN` conditions as you would like, but must have a `THEN` for every `WHEN.`

```
    CASE
    WHEN `State` = 'California'
    THEN 'CA'
    WHEN `State` = 'Washington'
    THEN 'WA'
    ELSE `State`
    END
```

### Date Formatting

The functions you can use are `DATE_FORMAT()` and `STR_TO_DATE()`. When using the \`STR

\_TO\_DATE()\` function you must use the exact same format your string column is already in so that the function can properly convert it to a date type.

For example, you have a string type column,`Date,` that needs to be converted to a date type. The format of your values is MM-DD-YYYY.

``STR_TO_DATE(`Date`,'%m-%d-%Y')``

If your values are formatted as YY/DD/MM, then use

``STR_TO-DATE(`Date`,'%y/%d/%m')``

After you have a date type column, if you want to change the format, use the `DATE_FORMAT()` function. This function can accept and generate any date format needed.

Your `Date` column is formatted as DD-MM-YY, but you would like to see it as MM-DD-YYYY

``DATE_FORMAT(`Date`,'%m-%d-%Y')``

To learn more about which date format specifiers to use, such as `%m` or `%y`, see [Date Format Specifier Characters in Beast Mode](/s/article/360043429953 "Date Format Specifier Characters in Beast Mode").

### Field name vs. String Value vs. Numeric Value.

In calculations, the actual field name is referenced using the backtick (\` ). Inputting a hard string value requires single quotes (‘ ), while referencing a numeric value requires no quotes.

```
CASE WHEN `Return Customer?` = 'Y' THEN 1
WHEN `Return Customer?` = 'N' THEN 0
END
```

<Note>
  **Note:** Putting single quotes (') around numeric values will NOT result in an error but will output the 1s and 0s as a string-type column.
</Note>

### Strings Compared to Dates

When using the `DATE_FORMAT()` function, this converts the value to a `varchar` datatype, also known as a string. This is problematic because you can longer use the value as a date such as for comparisons. If your column should be a `date` format after using the `DATE_FORMAT()` function, then you need to use `STR_TO_DATE()` to convert it back to a date type.

Using the following calculation, the output would be a string-type column.

``DATE_FORMAT(`Date`,'%m-%d-%Y')``

To convert the string to a date, we would want to wrap our entire calculation in the `STR_TO_DATE()` function.

``STR_TO_DATE(DATE_FORMAT(`Date`,'%m-%d-%Y'),'%m-%d-%Y')``

In the case that you have a string column that needs to be converted to a date column and you want it formatted differently, you must do the following:

``STR_TO_DATE(DATE_FORMAT(STR_TO_DATE(`Date`,'%y-%m-%d'),'%m-%d-%Y'),'%m-%d-%Y')``

Because it works from the inside out, the `` `Date` `` column, which is formatted as YY-MM-DD is first converted to a date. We would like to see the column as a MM-DD-YYYY format, so we then use `DATE_FORMAT()` to do so. However, now we are left with a string-type column. Lastly, we will use the STR

\_TO\_DATE() function to return it to a date type.

### Use of Trim Function

Many times, strings will leave unwanted spaces at the end or beginning of the value. This is problematic when using formulas that call out the exact value. The `trim()` function will trim off any unnecessary white space.

For example, if the `Zipcode` column contains values that have white space, such as '12345   '. Knowing white space exists in your values is extremely important if you are, for example, using the `COUNT()` function. `COUNT()` will count the number of characters in a string, including white space. By using the `trim()` function first, you ensure that you count only the number of valid characters in your `Zipcode` values.

``COUNT(TRIM(`Zipcode`))``