Can you have a card with calculated rows that have different data types under one column?

user074136 · 2021-06-02T12:42:43+00:00

There was an error rendering this rich post.

user074136

I have a financial report that shows:

Row: 1 Gross Retail Revenue ($ value)

Row 2: Retail Discounts ($ Value)

Row 3: % Retail Discount = Retail Discounts/ Gross Retail Revenue (% Value)

Row: 4 Gross Wholesale Revenue ($ value)

Row 5: Wholesale Discounts ($ Value)

Row 6: % Wholesale Discount = Wholesale Discounts/ Gross Wholesale Revenue (% Value)

While I was able to create a SQL transformation to get the calculations, the card requires a column to have only one data type ($ or %). I need this report to be formatted with these rows having different data types. Any help is greatly appreciated.

Thanks!

Find more posts tagged with

Accepted answers

All comments

jaeW_at_Onyx

@user074136

some cards under "general settings" have an option to "transpose" the data.

but in general

1) don't preaggregate your data using SQL, this will limit your ability to filter your data. instead try to use beast modes in analyzer as a surrogate for SQL aggregation

2) in one column, percent and currency are both numeric data types, so what you want to do is change formatting, which is a display question (use a CASE statement and build in the logic for a formatting string.)

something like this, https://youtu.be/YgevJkjeFqw?t=644 will get you started.

good luck!

GrantSmith

Hi @user074136

Domo doesn't allow for different data types in the same column. To get around this you can utilize a beast mode to customize the formatting of each row manually with several CASE statements:

CASE WHEN `row name` = 'Gross Retail Revenue` THEN CONCAT('$', ROUND(`value`, 2))
WHEN `row name` =  '% Retail Discount', THEN
CONCAT(ROUND(`value`, 2), '%')
...
END

user074136

@GrantSmith I have tried this approach, but this turns the data column into a text column and because I'm using a pivot table, the values section in this type of table has to be a numeric data type.

GrantSmith

You'd need to pivot your data yourself then outside of a pivot table card (using a dataflow or other mechanism) and utilize a standard table card with your pivoted data

user074136

@GrantSmith Thank you!