Need help with data organization

joebenz

I have P&L data that we currently have set up in it's final form in a spreadsheet that I'm trying to get into Domo and format it effectively. Thus, I'm looking for advice on the best way to handle this situation. Here is what our data looks like in the final spreadsheet.

First question is, what would be a better way to organize the data so that it can be manipulated easier in Domo?

One of the primary concerns is that we want to be able to format the resulting domo table card to have percentages where relevant and dollars where relevant.

Any advice on organization, formatting, etc is helpful - thanks!

ArborRose

I generally want all my data in detail statements. Each transaction with the account, category, amount, date, type, etc on each line. Then aggregate the amounts and compile it to a final output. Something like you show, I gather from millions of rows of transactional data. Using Magic ETL, to group and sum and categorize.

But based on the way you form your question, it reads as if you are getting data that is already aggregated.

Account	Category	Month	Value	Data Type
Revenue	Income	Jan 2024	50000	Dollars
Expense 1	Cost of Sales	Jan 2024	25000	Dollars
Expense Ratio 1	Cost of Sales	Jan 202	50%	Percent

With dates, I prefer the actual dates and use calculations to pull month and year using month(mydate), year(mydate), etc. But your data sounds like it comes as {month}{year}.

Use beast mode for logic and conditional formatting. You can create rules for applying percentages or dollar formatting depending upon the data type column. That is, if you aren't using the formatting on a card to format the output.

Group by account and aggregate the data by month. Use the data type column to determine if the cell should display as a percentage or currency.

CASE WHEN `Data Type` = 'Dollars' THEN CONCAT('$',FORMAT(SUM(`Value`), 2)) ELSE '' END

CASE WHEN `Data Type` = 'Percent' THEN CONCAT(FORMAT(SUM(`Value`), 2), '%') ELSE '' END

ArborRose

I generally want all my data in detail statements. Each transaction with the account, category, amount, date, type, etc on each line. Then aggregate the amounts and compile it to a final output. Something like you show, I gather from millions of rows of transactional data. Using Magic ETL, to group and sum and categorize.

But based on the way you form your question, it reads as if you are getting data that is already aggregated.

Account	Category	Month	Value	Data Type
Revenue	Income	Jan 2024	50000	Dollars
Expense 1	Cost of Sales	Jan 2024	25000	Dollars
Expense Ratio 1	Cost of Sales	Jan 202	50%	Percent

With dates, I prefer the actual dates and use calculations to pull month and year using month(mydate), year(mydate), etc. But your data sounds like it comes as {month}{year}.

Use beast mode for logic and conditional formatting. You can create rules for applying percentages or dollar formatting depending upon the data type column. That is, if you aren't using the formatting on a card to format the output.

Group by account and aggregate the data by month. Use the data type column to determine if the cell should display as a percentage or currency.

CASE WHEN `Data Type` = 'Dollars' THEN CONCAT('$',FORMAT(SUM(`Value`), 2)) ELSE '' END

CASE WHEN `Data Type` = 'Percent' THEN CONCAT(FORMAT(SUM(`Value`), 2), '%') ELSE '' END