Need help with data organization
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!
Best Answer
-
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
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Answers
-
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
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 709 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive