formatting in a table

mroker

I have a table like this:

Category | Actual | Budget

Revenue | 4.50 | 5.50

Sales | 4,250.00 | 5,550.00

For this table, I have in a pivot table, and I want the revenue to be formatted with the ($), and the sales, I want to not have decimals. How can I do this in a beast mode? If I format this for the column on the card it will format everything as one format.

ggenovese

Trying to apply a number format in a beast mode can be limiting, especially if you want to comma separate the thousands. It might be beneficial to restructure your data in an ETL so that Revenue and Sales are in their own columns.

ArborRose

There's no beast mode command for dealing with commas, but you can achieve it in other ways if desired.

CASE

  WHEN `Category` = 'Revenue' THEN 

    CONCAT('$', 

      CASE 

        WHEN ROUND(`Actual`, 2) >= 1000000 THEN CONCAT(ROUND(ROUND(`Actual`, 2) / 1000000, 2), 'M')

        WHEN ROUND(`Actual`, 2) >= 1000 THEN CONCAT(FLOOR(ROUND(`Actual`, 2) / 1000), ',', LPAD(ROUND(MOD(ROUND(`Actual`, 2), 1000), 2), 3, '0'))

        ELSE ROUND(`Actual`, 2)

      END

    )

  WHEN `Category` = 'Sales' THEN 

    CASE 

      WHEN ROUND(`Actual`, 0) >= 1000000 THEN CONCAT(FLOOR(ROUND(`Actual`, 0) / 1000000), 'M')

      WHEN ROUND(`Actual`, 0) >= 1000 THEN CONCAT(FLOOR(ROUND(`Actual`, 0) / 1000), ',', LPAD(ROUND(MOD(ROUND(`Actual`, 0), 1000), 0), 3, '0'))

      ELSE ROUND(`Actual`, 0)

    END

  ELSE ROUND(`Actual`, 2)

END

ArborRose

How about something like

CASE 

  WHEN `Category` = 'Revenue' THEN CONCAT('$', FORMAT(`Actual`, 2)) 

  WHEN `Category` = 'Sales' THEN FORMAT(`Actual`, 0)

  ELSE FORMAT(`Actual`, 2)

END

mroker

I keep getting this error.

ArborRose

Sorry…I'm not in Domo testing the function. I'll create a quick sample set and test.

CASE 

  WHEN `Category` = 'Revenue' THEN CONCAT('$', ROUND(`Actual`, 2)) 

  WHEN `Category` = 'Sales' THEN ROUND(`Actual`, 0)

  ELSE ROUND(`Actual`, 2)

END

ggenovese

Trying to apply a number format in a beast mode can be limiting, especially if you want to comma separate the thousands. It might be beneficial to restructure your data in an ETL so that Revenue and Sales are in their own columns.

mroker

https://community-forums.domo.com/main/discussion/comment/98976#Comment_98976

I think this may be the answer although it's disappointing I can't do it this way.

ArborRose

There's no beast mode command for dealing with commas, but you can achieve it in other ways if desired.

CASE

  WHEN `Category` = 'Revenue' THEN 

    CONCAT('$', 

      CASE 

        WHEN ROUND(`Actual`, 2) >= 1000000 THEN CONCAT(ROUND(ROUND(`Actual`, 2) / 1000000, 2), 'M')

        WHEN ROUND(`Actual`, 2) >= 1000 THEN CONCAT(FLOOR(ROUND(`Actual`, 2) / 1000), ',', LPAD(ROUND(MOD(ROUND(`Actual`, 2), 1000), 2), 3, '0'))

        ELSE ROUND(`Actual`, 2)

      END

    )

  WHEN `Category` = 'Sales' THEN 

    CASE 

      WHEN ROUND(`Actual`, 0) >= 1000000 THEN CONCAT(FLOOR(ROUND(`Actual`, 0) / 1000000), 'M')

      WHEN ROUND(`Actual`, 0) >= 1000 THEN CONCAT(FLOOR(ROUND(`Actual`, 0) / 1000), ',', LPAD(ROUND(MOD(ROUND(`Actual`, 0), 1000), 0), 3, '0'))

      ELSE ROUND(`Actual`, 0)

    END

  ELSE ROUND(`Actual`, 2)

END

mroker

https://community-forums.domo.com/main/discussion/comment/98981#Comment_98981

I will try this. Does this approach work for removing decimal places as well? Also when I use format in beast modes i get syntax errors as it indicates Domo does not support this function.