Stock Open Opportunities by Active Account Executives in a period
I need some help here. I have a dataset of active employees in a specific date or period builded with a snapshot dataset day by day.
By the other hand, I have a dataset of opportunities, with "Date Created" and "Closed Date".
If an opportunity has a "Date Created" less than or equal to "active period" and Closed Date is empty or greater than active period, it was or it is an open opportunity.
I need to find a way to get the number of open opportunity in a date range, not just in a specific date.
Here an example of the opportunities dataset:
Here is what I expect:
I'll appreciate any help! Thank you all!
I'd recommend you restructure your data such that you have one record for each day and opportunity.
You can do this by using the Domo date dimension table (Connector -> Domo Dimensions - Select calendar) and a mysql DataFlow (Magic doesn't allow between joins yet)
A pseudo example would look like
select `dt`, `opportunity` from dates d -- This is your domo date dimension table join opportunities o on d.`dt`>=o.`start_date` and d.`dt`<=o.`end_date`
This will then list all of the dates an opportunity is active for. You can then take this dataset and join it to your employee table based on their activity date to get the employees active on a specific date for a given opportunity.**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
@gespindola , @GrantSmith 's idea is solid. only thing to be sure of is that there actually are start and end dates. if one column isn't populated it could cause a row to drop. consider adding a default start or end date if null.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"1
Hello - can you provide any additional information on this? Like, for example, what was the purpose of describing the first table? I'm having trouble understanding how it's used in the final result. Also, how are the periods of interest in your expected dataset stored? and why are they sometimes ranges of dates and other times just single dates?0
Hi Giacomo, Thanks for your response.
The reason I described the first table was because I need to see the Active Employees in a specific period.
- all active employees between Jan 1, 2021 and Jan 3, 2021 = 2 (A and B)
- all active employees on Jan 2, 2021 = 2 (A and B)
Then I have opportunities, so if I select "Between" Jan 1, 2021 and Jan 4, 2021 in the card, it should be:
A - All Active Employees between Jan 1, 2021 and Jan 4, 2021 (total = 2)
B - All Open Opportunities between Jan 1, 2021 and Jan 4, 2021. But an opportunity could be closed at Jan 2, so the open opportunities should be only those with Date Created less or equal than Jan 1 and Closed greater than Date Jan 4.
Each bar should show me B / A
I hope i explained myself well. Attached you can find and example of what I need (the data is the card is just a test)
Thank you in advanceGuillermo Espindola0
Thank you for your help guys! It worked :) @jaeW_at_Onyx , @GrantSmithGuillermo Espindola0
- 7.7K All Categories
- 12 Connect
- 925 Connectors
- 247 Workbench
- 441 Transform
- 1.7K Magic ETL
- 61 SQL DataFlows
- 456 Datasets
- 88 Visualize
- 222 Beast Mode
- 2.1K Charting
- 8 Variables
- 35 Cards, Dashboards, Stories
- 5 Automate
- 349 APIs & Domo Developer
- 85 Apps
- 17 Predict
- 3 Jupyter Workspaces
- 14 R & Python Tiles
- 242 Distribute
- 60 Domo Everywhere
- 241 Scheduled Reports
- 18 Manage
- 39 Governance & Security
- 46 Product Ideas
- 1.1K Ideas Exchange
- 6 Community Forums
- 19 Getting Started
- 6 Community Member Introductions
- 50 Community News
- 18 Event Recordings
- 577 日本支部