Formatting for Variables that have different ranges

Shevy

I have a data set with a variable where the user can select a metric to view. Some choices are dollars / values and others are percentages. Any way to get the format to change depending on the metric chosen? As a side question to this, could the conditional formatting of the table be different based on the metric?

ggenovese

One option is to create a beast mode with a case statement that applies a dynamic format based on the variable. There is a MySQL Function called FORMAT() that you can use in a beast mode even though it's not a documented beast mode function and it will give you a validation error.

Something like this:

CASE 
WHEN `My Variable` = '$ Sold' THEN CONCAT('$ ', FORMAT(SUM(`Sold`),2)))
WHEN `My Variable` = '% Change' THEN CONCAT(FORMAT(((SUM(`Current Value`) - SUM(`Previous Value`)) / SUM(`Previous Value`))*100,2),'%')
ELSE FORMAT(SUM(`Units`),0)
END

ggenovese

One option is to create a beast mode with a case statement that applies a dynamic format based on the variable. There is a MySQL Function called FORMAT() that you can use in a beast mode even though it's not a documented beast mode function and it will give you a validation error.

Something like this:

CASE 
WHEN `My Variable` = '$ Sold' THEN CONCAT('$ ', FORMAT(SUM(`Sold`),2)))
WHEN `My Variable` = '% Change' THEN CONCAT(FORMAT(((SUM(`Current Value`) - SUM(`Previous Value`)) / SUM(`Previous Value`))*100,2),'%')
ELSE FORMAT(SUM(`Units`),0)
END

Manasi_Panov

Hi @Shevy,

For charts and graphs, use a separate Beast Mode with the Tooltip function to dynamically switch between %, $, or units.

Shevy

Don't love this solution but it can work. Thank you