DP22 - Using Beast Mode to Build Data Storytelling - Formatting Text

GrantSmith

This post is part of a series of beast modes I presented on during my “Using Beast Mode to Build Data Storytelling” Domopalooza 2022 session. If you find this post helpful please make sure to give it a like or an awesome below.

Use Case:

We wanted a way to allow our users to quickly visualize good or bad data results. To do this we would conditionally set the different values in an HTML table to different colors depending on the different conditions we defined.

Beast Modes:

Custom Color Definition

CONCAT('<span style="color: ',
CASE WHEN MOD(FLOOR(SUM(`Daily Sales`)), 2) = 0 THEN 'green' ELSE 'red' END,
  '">',
  FLOOR(SUM(`Daily Sales`)),
  '</span>’)

Formatting Numbers with Commas

CONCAT('<span style="color: ',
CASE WHEN MOD(FLOOR(SUM(`Daily Sales`)), 2) = 0 THEN 'green' ELSE 'red' END,
  '">$',
CASE WHEN SUM(`Daily Sales`) < 0 THEN '-' ELSE '' END,
CASE WHEN ABS(SUM(`Daily Sales`)) > 100000000000 THEN
  CONCAT(
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 1, 3), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 4, 3), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 7, 3), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 10, 3)
  )
WHEN ABS(SUM(`Daily Sales`)) >= 10000000000 THEN
  CONCAT(
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 1, 2), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 3, 3), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 6, 3), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 9, 3)
  )
WHEN ABS(SUM(`Daily Sales`)) >= 1000000000 THEN
  CONCAT(
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 1, 1), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 2, 3), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 5, 3), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 7, 3)
  )
WHEN ABS(SUM(`Daily Sales`)) >= 100000000 THEN
  CONCAT(
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 1, 3), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 4, 3), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 7, 3)
  )
WHEN ABS(SUM(`Daily Sales`)) >= 10000000 THEN
  CONCAT(
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 1, 2), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 3, 3), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 6, 3)
  )
WHEN ABS(SUM(`Daily Sales`)) >= 1000000 THEN
  CONCAT(
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 1, 1), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 2, 3), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 5, 3)
  )
WHEN ABS(SUM(`Daily Sales`)) >= 100000 THEN
  CONCAT(
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 1, 3), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 4, 3)
  )
WHEN ABS(SUM(`Daily Sales`)) >= 10000 THEN
  CONCAT(
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 1, 2), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 3, 3)
  )
WHEN ABS(SUM(`Daily Sales`)) >= 1000 THEN
  CONCAT(
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 1, 1), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 2, 3)
  )
WHEN ABS(SUM(`Daily Sales`)) < 1000 THEN
  ROUND(SUM(`Daily Sales`), 0)
ELSE
  'Unsupported number length'
END,
  '</span>')

Formatting Abbreviated Number

CONCAT('<div style="color: ',
CASE WHEN MOD(FLOOR(SUM(`Daily Sales`)), 2) = 0 THEN 'green' ELSE 'red' END,
  '">$',
  CASE WHEN ABS(`Daily Sales`) >= 1000000000 THEN
  CONCAT(ROUND(SUM(`Daily Sales`) / 1000000000, 2), 'B')
  -- 7 digits means Millions
  WHEN ABS(`Daily Sales`) >= 1000000 THEN
  CONCAT(ROUND(SUM(`Daily Sales`) / 1000000, 2), 'M')
  -- 4-6 digits = thousands
  WHEN ABS(`Daily Sales`) >= 1000 THEN
  CONCAT(ROUND(SUM(`Daily Sales`) / 1000, 2), 'K')
  WHEN ABS(`Daily Sales`) < 1000 THEN
  ROUND(SUM(`Daily Sales`), 0)
END,
  '</div>')

Beast Mode Breakdown:

Custom Color Definition

<span style="color: ' … >Text To Color</span>

Here we utilize some HTML code to define a container to put the text we want to color into. The HTML code specifies a style to define which color we wish to utilize

CASE WHEN MOD(FLOOR(SUM(`Daily Sales`)), 2) = 0 THEN 'green' ELSE 'red' END

This is where we defined the conditions used to color the different text. In this case it’s checking to see if the daily sales is even, if it is make the color green otherwise make the color red however you can put whatever logic you wish here. Hexadecimal codes are also allowed as color values.

FLOOR(SUM(`Daily Sales`))

The value wrapped in between the <span></span> tags is what will be colored.

Formatting Numbers with Commas

CONCAT('<span style="color: ',
CASE WHEN MOD(FLOOR(SUM(`Daily Sales`)), 2) = 0 THEN 'green' ELSE 'red' END,
  '">$',
CASE WHEN SUM(`Daily Sales`) < 0 THEN '-' ELSE '' END,
CASE WHEN ABS(SUM(`Daily Sales`)) > 100000000000 THEN
  CONCAT(
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 1, 3), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 4, 3), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 7, 3), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 10, 3)
  )
...
WHEN ABS(SUM(`Daily Sales`)) < 1000 THEN
  ROUND(SUM(`Daily Sales`), 0)
ELSE
  'Unsupported number length'
END,
  '</span>')

Here we utilize a CASE statement to determine the value / length of the number we wish to format. Depending on the length it varies the different positions to extract out of the number utilizing the SUBSTRING command and concatenate each section of the string with commas. This example supports numbers up to 999 Billion however it can be expanded for larger numbers.

CASE WHEN SUM(`Daily Sales`) < 0 THEN '-' ELSE '' END

This is handling the negative numbers in order to simplify the beast mode logic so we only need to do one length condition instead of having to deal with positive and negative numbers.

CASE WHEN ABS(SUM(`Daily Sales`)) > 100000000000 THEN
  CONCAT(
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 1, 3), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 4, 3), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 7, 3), ',',
  SUBSTRING(ROUND(SUM(`Daily Sales`), 0), 10, 3)
  )

ABS is used to get the absolute value, again for simplifying the logic of the beast mode.

SUBSTRING is used to get a substring or piece of the number. In this case we’re getting the first 3 digits since we’re in the hundreds of billions, followed by the 3 digits for the millions, then thousands and hundreds.

The case statements are checking lengths in descending order. This is to simplify the beast mode further because the case statement will evaluate the first true clause and ignore anything after. For example if the number is 10,000 it’s checking for >= 10,000 but also implies < 100,000 because it's evaluated after the >= 100,000 condition.

Formatting Abbreviated Number

CONCAT('<div style="color: ',
CASE WHEN MOD(FLOOR(SUM(`Daily Sales`)), 2) = 0 THEN 'green' ELSE 'red' END,
  '">$',
  CASE WHEN ABS(`Daily Sales`) >= 1000000000 THEN
  CONCAT(ROUND(SUM(`Daily Sales`) / 1000000000, 2), 'B')
  -- 7 digits means Millions
  WHEN ABS(`Daily Sales`) >= 1000000 THEN
  CONCAT(ROUND(SUM(`Daily Sales`) / 1000000, 2), 'M')
  -- 4-6 digits = thousands
  WHEN ABS(`Daily Sales`) >= 1000 THEN
  CONCAT(ROUND(SUM(`Daily Sales`) / 1000, 2), 'K')
  WHEN ABS(`Daily Sales`) < 1000 THEN
  ROUND(SUM(`Daily Sales`), 0)
END,
  '</div>')

Similar logic here however we are just dividing by the largest possible unit (Billions, Millions, Thousands) in descending order.

ROUND(.. ,2) rounds our number to two decimal places. This can be changed to 0, 1 or any number of decimal places you need for your use case.

CONCAT will append our unit of measurement (B, M, K) to the end of the rounded number

Final Result:

GrantSmith

In an effort to more simplify the comma delimited version of the beast mode I'm posting an alternative version instead of having to check every single different potential lengths it will automatically calculate the different sections automatically making the beast mode more readable. This version also handles numbers up to the quadrillions (21 digits). It also reduces the original version of the beast mode from roughly 60 lines to about 9 and increases the digits of precision (billions -> quadrillions).

REPLACE(TRIM(CONCAT(
  SUBSTRING(`Number`, 1, CASE WHEN MOD(LENGTH(`Number`), 3) = 0 then 3 ELSE MOD(LENGTH(`Number`), 3) END), ' ',
  SUBSTRING(`Number`, 1 + (3*0) + CASE WHEN MOD(LENGTH(`Number`), 3) = 0 then 3 ELSE MOD(LENGTH(`Number`), 3) END, 3), ' ',
  SUBSTRING(`Number`, 1 + (3*1) + CASE WHEN MOD(LENGTH(`Number`), 3) = 0 then 3 ELSE MOD(LENGTH(`Number`), 3) END, 3), ' ',
  SUBSTRING(`Number`, 1 + (3*2) + CASE WHEN MOD(LENGTH(`Number`), 3) = 0 then 3 ELSE MOD(LENGTH(`Number`), 3) END, 3), ' '),
  SUBSTRING(`Number`, 1 + (3*3) + CASE WHEN MOD(LENGTH(`Number`), 3) = 0 then 3 ELSE MOD(LENGTH(`Number`), 3) END, 3), ' '),
  SUBSTRING(`Number`, 1 + (3*4) + CASE WHEN MOD(LENGTH(`Number`), 3) = 0 then 3 ELSE MOD(LENGTH(`Number`), 3) END, 3), ' '),
  SUBSTRING(`Number`, 1 + (3*5) + CASE WHEN MOD(LENGTH(`Number`), 3) = 0 then 3 ELSE MOD(LENGTH(`Number`), 3) END, 3), ' ')
), ' ', ',')

This is a simplified version which only handles positive whole numbers but could be expanded to handle negative numbers and decimals.