formatting in a table

mroker · October 21

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 · October 21

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 · October 21

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 · October 21

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 · October 21

I keep getting this error.

ArborRose · October 21

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 · October 21

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 · October 21

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 · October 21

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 · October 29

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.

formatting in a table

Best Answers

Answers

Categories