turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Dojo
- :
- Using Domo
- :
- Beast Mode, ETL/Dataflow
- :
- Formatting Multiple Summary Numbers with Commas

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##
##### Formatting Multiple Summary Numbers with Commas

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

04-12-2018
10:59 AM

04-12-2018
10:59 AM

**Problem**

To build summary numbers with multiple metrics, one needs to use the CONCAT operator, which converts the entire output to a string. This means that numbers won’t contain comma’s. In this case, a solution is needed to dynamically add comma’s to these numbers based on their length.

For example, in the Summary Number below the **first number has no commas**. The second number being a percentage is easier to handle because it is rounded to one decimal precision and never needs a comma because it never exceeds the hundreds.

** **

*Desired output:*

**Solution**

- Find the Length of the Number
- Break it into appropriate comma blocks
- Concatenate commas between the blocks.

For example, a four-digit number is in the thousands and would have two sections: the first number followed by a comma and then the last three numbers: [1],[234]

This can be handling using a combination of LENGTH() to get the overall length of the number, ROUND() to remove decimal precision and SUBSTRING() to pick out the position of the characters you want to place in each comma block. In addition, it will need to be handled using nested CASE logic to identify each case and handle it appropriately. Lastly, it will be tied together with another CONCAT().

**Code**

Here is a sample of code that demonstrates how to format one metric up to hundreds of millions - 9 digits in length. Since you will have multiple summary numbers, you would need to do this for each numeric value in the summary number.

CASE

WHEN LENGTH(ROUND(SUM(`Measure Column`))) = 9 -- 9 digits = hundreds of millions

THEN

CONCAT(

SUBSTRING(ROUND(SUM(`Measure Column`)),1,3), -- first comma block

',',

SUBSTRING(ROUND(SUM(`Measure Column`)), 3,3), -- second comma block

',',

SUBSTRING(ROUND(SUM(`Measure Column`)),6,3) -- third comma block

)

WHEN LENGTH(ROUND(SUM(`Measure Column`))) = 8 -- 8 digits = tens of millions

THEN

CONCAT(

SUBSTRING(ROUND(SUM(`Measure Column`)),1,3), -- first comma block

',',

SUBSTRING(ROUND(SUM(`Measure Column`)), 2,3), -- second comma block

',',

SUBSTRING(ROUND(SUM(`Measure Column`)),6,3) -- third comma block

)

WHEN LENGTH(ROUND(SUM(`Measure Column`))) = 7 -- 7 digits = millions

THEN

CONCAT(

SUBSTRING(ROUND(SUM(`Measure Column`)),1,1), -- first comma block

',',

SUBSTRING(ROUND(SUM(`Measure Column`)), 2,3), -- second comma block

',',

SUBSTRING(ROUND(SUM(`Measure Column`)),5,3) -- third comma block

)

WHEN LENGTH(ROUND(SUM(`Measure Column`))) = 6 -- 6 digits = hundreds of thousands

THEN

CONCAT(

SUBSTRING(ROUND(SUM(`Measure Column`)),1,3), -- first comma block

',',

SUBSTRING(ROUND(SUM(`Measure Column`)), 4,3) -- second comma block

)

WHEN LENGTH(ROUND(SUM(`Measure Column`))) = 5 -- 5 digits = tens of thousands

THEN

CONCAT(

SUBSTRING(ROUND(SUM(`Measure Column`)),1,2), -- first comma block

',',

SUBSTRING(ROUND(SUM(`Measure Column`)), 3,3) -- second comma block

)

WHEN LENGTH(ROUND(SUM(`Measure Column`))) = 4 -- 4 digits = thousands

THEN

CONCAT(

SUBSTRING(ROUND(SUM(`Measure Column`)),1,1), -- first comma block

',',

SUBSTRING(ROUND(SUM(`Measure Column`)), 2,3) -- second comma block

)

WHEN LENGTH(ROUND(SUM(`Measure Column`))) < 4 -- < 4 digits = hundreds or less

THEN ROUND(SUM(`Measure Column`))

END

Jacob Folsom

**Say “Thanks” by clicking the “heart” in the post that helped you.

**Please mark the post that solves your problem by clicking on "Accept as Solution"

**Say “Thanks” by clicking the “heart” in the post that helped you.

**Please mark the post that solves your problem by clicking on "Accept as Solution"

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

04-17-2018
02:03 PM

04-17-2018
02:03 PM

Great article thanks for sharing!

Dani

Dojo Admin

**Say "Thanks" by clicking the "heart" in the post that helped you.

**Please mark the post that solves your problem by clicking on "Accept as Solution"

Dojo Admin

**Say "Thanks" by clicking the "heart" in the post that helped you.

**Please mark the post that solves your problem by clicking on "Accept as Solution"