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

user074136
user074136
in Charting

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 Coach

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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • GrantSmith
    GrantSmith Coach

    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


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • 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 Coach

    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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • user074136
    user074136

    @GrantSmith Thank you!

Categories