Historical Trend
We have sales data that is just a list of sales opportunities like so
Opportunity → Create Date, Close Date, Amount
We have all opportunities (open and closed) but we only have the current status of the Opportunity.
Can i build a historical trend from this simple data set, so I can see what the total opportunity amount was on a monthly basis? It will basically need to be the sum of all opportunities where that month is between the open and closed date.
Best Answer
-
I would suggest a re-structure of the data to something like this:
Then you could do a waterfall:
Or a running total bar, which sounds more literally what you're asking for:
Here's the MagicETL I used to restructure the data:
Please 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
1
Answers
-
On your initial dataset, you can it to append instead of replace, which will allow you to build a historical trend.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
It'd depend on what Monthly basis means to you, and what the Amount should be. If you want the opportunity to be counted on each month it was open (as of the end of the month), I'd suggest doing a ETL to Join your table with the DOMO Calendar one in a FULL OUTER join fashion (only joining the EoM dates), and then filter out the entries that that fall outside the range. That way you'll get based on the current information, which of those opps where open at the end of each month. You can avoid filtering and just use a formula instead to categorize them as either:
- non-existent, if EoM is prior to opp creation
- open, if EoM is after creation but prior to closure
- closed legacy, if EoM is after close date but not the same month
- close current, if EoM is the same month as the close date
The last approach is going to make for a huge table unless you define date boundaries, but it would allow you to have more insights as to what is going on. Please keep in mind that all of this will be basically rebuilding historical based on current values, so it won't account for opportunity Amount changes overtime, for that you'd need real snapshots or amount history to rebuilt them with more complex ETLs or transformations.
0 -
So i want to visualize the total pipeline (total of all opportunities that were open on any given date).
So in this case, on October 1, the total pipeline was $200 (None of the opportunities had closed). But August 1 the pipeline was only $150 (Opportunity B had not been created).
0 -
I would suggest a re-structure of the data to something like this:
Then you could do a waterfall:
Or a running total bar, which sounds more literally what you're asking for:
Here's the MagicETL I used to restructure the data:
Please 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 126 Manage
- 123 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive