Help on Beast Mode Concat Format

David_Sachs
David_Sachs Member
in Magic ETL

Current I get for a Summary 2711 Meetings and $143043062 Total Est. Budget but I am looking for the $ amount to include comma separators and two decimal. Am using a beast mode of CONCAT(COUNT(`Meeting Identifier`),' Meetings and $', SUM(`Total Estimated Budget`),' Total Est. Budget') and I am a novice and painfully stuck. Any help is apprecaited. 

Best Answer

  • ST_Superman
    ST_Superman Domo Employee
    Answer ✓

    What we could really use here is the FORMAT() function.  @DaniBoy  (hint, hint)

     

    However, as that is not an option for beastmodes, we will have to get more creative with our solution.

     

    It's going to look something like this... but I have not had the time to test it out:

    CONCAT(
      COUNT(`Meeting Identifier`)
      ,' Meetings and $'
      ,left(
       round(sum(`Total Estimated Budget`),2),
       MOD(
          (LENGTH(round(sum(`Total Estimated Budget`),2))),3
          ))
       ,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >1 then ',' else '.' end
      ,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >1 then SUBSTRING(round(sum(`Total Estimated Budget`),2),MOD(
          (LENGTH(round(sum(`Total Estimated Budget`),2))),3
          )+1,3) else RIGHT(round(SUM(`Total Estimated Budget`),2),2) end
      ,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >2 then ',' end
      ,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >2 then SUBSTRING(round(sum(`Total Estimated Budget`),2),MOD(
          (LENGTH(round(sum(`Total Estimated Budget`),2))),3
          )+1,3) else RIGHT(round(SUM(`Total Estimated Budget`),2),2) end 
       ,' Total Est. Budget'
       )

     

    I did get this to work with my sample data:1.png

     

Answers

  • ST_Superman
    ST_Superman Domo Employee
    Answer ✓

    What we could really use here is the FORMAT() function.  @DaniBoy  (hint, hint)

     

    However, as that is not an option for beastmodes, we will have to get more creative with our solution.

     

    It's going to look something like this... but I have not had the time to test it out:

    CONCAT(
      COUNT(`Meeting Identifier`)
      ,' Meetings and $'
      ,left(
       round(sum(`Total Estimated Budget`),2),
       MOD(
          (LENGTH(round(sum(`Total Estimated Budget`),2))),3
          ))
       ,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >1 then ',' else '.' end
      ,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >1 then SUBSTRING(round(sum(`Total Estimated Budget`),2),MOD(
          (LENGTH(round(sum(`Total Estimated Budget`),2))),3
          )+1,3) else RIGHT(round(SUM(`Total Estimated Budget`),2),2) end
      ,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >2 then ',' end
      ,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >2 then SUBSTRING(round(sum(`Total Estimated Budget`),2),MOD(
          (LENGTH(round(sum(`Total Estimated Budget`),2))),3
          )+1,3) else RIGHT(round(SUM(`Total Estimated Budget`),2),2) end 
       ,' Total Est. Budget'
       )

     

    I did get this to work with my sample data:1.png

     

  • David_Sachs
    David_Sachs Member

    ST_-Superman- - Endless thanks - worked like a charm. Now I just need to dive in and understand it for future use. You made me and my client happy campers. Very much apprecaited. And I have submitted FORMAT as an enhancement request as I know many others have also done. 

Categories