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

user074136
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!

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟀

    @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
    GrantSmith Indiana 🟀

    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
    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
    GrantSmith Indiana 🟀

    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
    user074136 βšͺ️

    @GrantSmith Thank you!

Sign In or Register to comment.