Issue with Sales Data Tracking and Date Transformation
Hello,
I'm encountering an issue with tracking sales data in from our CRM. The problem arises from the fact that the table card relies on the 'Date Created' column for capturing engagements. Although this successfully captures most sales data, it fails to include engagements that were created in the previous year but completed in the current year.
I would appreciate any input or suggestions on how I can modify my transformation to capture the following:
1.All engagements created in the current year.
2.Engagements that were completed in the current year, regardless of their creation date.
Data columns from this dataset include:
Date Created
Sales Date
Current Date
Thank you for your assistance!
Best Answer
-
You'll need to stack / pivot your data so you have a singular date field instead of multiple per row.
You data would look something like:
Order ID | Date | Date Type | Order Total
1 | 1/1/2023 | Created | $123
1 | 1/7/2023 | Sales | $ 123
Then you can use the new Date field to make sure you're getting records affected by each. If this is single line sales data it can get duplicated if it happens in the same year so you'll need to dedupe your data or consider it as part of your calculations. You can use a window function to divide your order amounts so that when the multiple lines get added together it'll add up correctly.
Something like this might work.
SUM(`Order Total` / COUNT(`Order ID`) OVER PARTITION BY `Order ID`)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers
-
You'll need to stack / pivot your data so you have a singular date field instead of multiple per row.
You data would look something like:
Order ID | Date | Date Type | Order Total
1 | 1/1/2023 | Created | $123
1 | 1/7/2023 | Sales | $ 123
Then you can use the new Date field to make sure you're getting records affected by each. If this is single line sales data it can get duplicated if it happens in the same year so you'll need to dedupe your data or consider it as part of your calculations. You can use a window function to divide your order amounts so that when the multiple lines get added together it'll add up correctly.
Something like this might work.
SUM(`Order Total` / COUNT(`Order ID`) OVER PARTITION BY `Order ID`)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 307 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3K Transform
- 112 SQL DataFlows
- 649 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 790 Beast Mode
- 78 App Studio
- 43 Variables
- 745 Automate
- 187 Apps
- 475 APIs & Domo Developer
- 67 Workflows
- 16 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 406 Distribute
- 117 Domo Everywhere
- 279 Scheduled Reports
- 10 Software Integrations
- 139 Manage
- 136 Governance & Security
- 8 Domo Community Gallery
- 47 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 113 Community Announcements
- 4.8K Archive