Formatting Numeric Values within CONCAT functions to Currency

amehdad
amehdad Coach
in Charting

Hi there!

In one of my graphs, I have created a few beast modes involving nested CONCAT functions so I can capture many fields in a hover text. Some of the fields in the CONCAT are numeric but I would like them displayed as currency. After running the beast modes, the numbers are displayed as a long integer with no thousand separator etc. I understand that using my quantitative fields in this way means that I cannot use the 'Display as' format option in the Analyzer. I was wondering if there is something I can do prior to the Beast Mode CONCAT, such as an ETL node or mySQL code?

Cheers

Tagged:

Best Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    I recommend formatting it to currency with the concat function in a beast mode. If you try and do it in an ETL, then they would become strings and the card wouldn't be able to sum them when a user is interacting with the card. Here is a beast mode that will format it for you and abbreviate the millions and thousands:

    CONCAT('$',
  (CASE       WHEN INSTR(SUM(`totalreported`),'.') = 10 THEN /*xxx million*/ 
                               CONCAT(LEFT(SUM(`totalreported`),3),'.',SUBSTRING(SUM(`totalreported`),4,2),'M')
       WHEN INSTR(SUM(`totalreported`),'.') = 9 THEN /*xx million*/ 
                               CONCAT(LEFT(SUM(`totalreported`),2),'.',SUBSTRING(SUM(`totalreported`),3,2),'M')
       WHEN INSTR(SUM(`totalreported`),'.') = 8 THEN /*x million*/ 
                               CONCAT(LEFT(SUM(`totalreported`),1),'.',SUBSTRING(SUM(`totalreported`),2,2),'M')
       WHEN INSTR(SUM(`totalreported`),'.') = 7 THEN /*xxx thousand*/ 
                               CONCAT(LEFT(SUM(`totalreported`),3),'.',SUBSTRING(SUM(`totalreported`),4,2),'K')
       WHEN INSTR(SUM(`totalreported`),'.') = 6 THEN /*xx thousand*/ 
                               CONCAT(LEFT(SUM(`totalreported`),2),'.',SUBSTRING(SUM(`totalreported`),3,2),'K')
       WHEN INSTR(SUM(`totalreported`),'.') = 5 THEN /*x thousand*/ 
                               CONCAT(LEFT(SUM(`totalreported`),1),'.',SUBSTRING(SUM(`totalreported`),2,2),'K')
       END))

    Here are two other examples of how to do it.

    https://dojo.domo.com/discussion/52685/domo-idea-exchange-beast-modes-abbreviated-summary-number#latest


    https://dojo.domo.com/discussion/52677/domo-ideas-conference-beast-modes-number-formatting#latest

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    I recommend formatting it to currency with the concat function in a beast mode. If you try and do it in an ETL, then they would become strings and the card wouldn't be able to sum them when a user is interacting with the card. Here is a beast mode that will format it for you and abbreviate the millions and thousands:

    CONCAT('$',
  (CASE       WHEN INSTR(SUM(`totalreported`),'.') = 10 THEN /*xxx million*/ 
                               CONCAT(LEFT(SUM(`totalreported`),3),'.',SUBSTRING(SUM(`totalreported`),4,2),'M')
       WHEN INSTR(SUM(`totalreported`),'.') = 9 THEN /*xx million*/ 
                               CONCAT(LEFT(SUM(`totalreported`),2),'.',SUBSTRING(SUM(`totalreported`),3,2),'M')
       WHEN INSTR(SUM(`totalreported`),'.') = 8 THEN /*x million*/ 
                               CONCAT(LEFT(SUM(`totalreported`),1),'.',SUBSTRING(SUM(`totalreported`),2,2),'M')
       WHEN INSTR(SUM(`totalreported`),'.') = 7 THEN /*xxx thousand*/ 
                               CONCAT(LEFT(SUM(`totalreported`),3),'.',SUBSTRING(SUM(`totalreported`),4,2),'K')
       WHEN INSTR(SUM(`totalreported`),'.') = 6 THEN /*xx thousand*/ 
                               CONCAT(LEFT(SUM(`totalreported`),2),'.',SUBSTRING(SUM(`totalreported`),3,2),'K')
       WHEN INSTR(SUM(`totalreported`),'.') = 5 THEN /*x thousand*/ 
                               CONCAT(LEFT(SUM(`totalreported`),1),'.',SUBSTRING(SUM(`totalreported`),2,2),'K')
       END))

    Here are two other examples of how to do it.

    https://dojo.domo.com/discussion/52685/domo-idea-exchange-beast-modes-abbreviated-summary-number#latest


    https://dojo.domo.com/discussion/52677/domo-ideas-conference-beast-modes-number-formatting#latest

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Coach

    @MarkSnodgrass beat me to it :) I’d recommend his. Those are some good articles he posted haha.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Coach

    Hat tip to @GrantSmith who wrote the articles, but I didn't initially call out because he is supposed to be on vacation and not on the Dojo. :)

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Coach

    @MarkSnodgrass apparently I can’t stay away…Can’t let you catch up :)

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories