Custom Summary Number using Beast Mode with Thousands Separators

user03400
user03400
in Data Platform & Data Science

Hello, I'm interested in using thousands separators in a custom summary number I built using a Beast Mode. The number to be separated is a summation using a case statement. For example, the number is 122435453.88 and I want the thousands separators. Thanks! 

Comments

  • RGranada
    RGranada 🟢

    Hi,

     

    I think the following beast mode will do the trick:

     

    CASE WHEN Length(CONCAT(ABS(ROUND(Your_Field_Name,0)),'')) = 9 THEN
    CONCAT(SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),''
    ),1,3),',',SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),''
    ),3,3),',',SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),'' ),6,3))
    WHEN Length(CONCAT(ABS(ROUND(Your_Field_Name,0)),'')) = 8 THEN
    CONCAT(SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),''
    ),1,2),',',SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),''
    ),3,3),',',SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),'' ),6,3))
    WHEN Length(CONCAT(ABS(ROUND(Your_Field_Name,0)),'')) = 7 THEN
    CONCAT(SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),''
    ),1,1),',',SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),''
    ),2,3),',',SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),'' ),5,3))
    WHEN Length(CONCAT(ABS(ROUND(Your_Field_Name,0)),'')) = 6 THEN
    CONCAT(SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),''
    ),1,3),',',SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),'' ),4,3))
    WHEN Length(CONCAT(ABS(ROUND(Your_Field_Name,0)),'')) = 5 THEN
    CONCAT(SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),''
    ),1,2),',',SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),'' ),3,3))
    WHEN Length(CONCAT(ABS(ROUND(Your_Field_Name,0)),'')) = 4 THEN
    CONCAT(SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),''
    ),1,1),',',SUBSTRING(CONCAT(Round((ABS(Your_Field_Name)),0),'' ),2,3))
    WHEN Length(CONCAT(ABS(ROUND(Your_Field_Name,0)),'')) <= 3 THEN
    CONCAT(Round(ABS(Your_Field_Name),0),'')
    END

     

    Tell me how it goes.

     

    Hope this helps.

    Ricardo Granada 

    [email protected]

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • Kay
    Kay ⚪️

    Hi there,

     

    wow. this is a lot of beast mode for what I hope will be a short function call someday Smiley Wink

     

    I did something similar - and used a part of your code - THANKS - because I want abbreviations only past >9999:

     

    (CASE 
    WHEN SUM(YOUR_FIELD) > 999999 THEN CONCAT (ROUND(SUM(YOUR_FIELD)/1000000), 'M')
    WHEN SUM(YOUR_FIELD) > 9999 THEN CONCAT (ROUND(SUM(YOUR_FIELD)/1000), 'K')
    WHEN SUM(YOUR_FIELD) > 999 THEN CONCAT(SUBSTRING(CONCAT(Round((SUM(YOUR_FIELD)),0),''
    ),1,1),',',SUBSTRING(CONCAT(Round((SUM(YOUR_FIELD)),0),'' ),2,3))
    ELSE SUM(YOUR_FIELD)
    END)

    Perhaps it helps somebody else...

     

    Thanks !

     

     

Categories