Stock Open Opportunities by Active Account Executives in a period

gespindola
gespindola Member
in APIs & Domo Developer

Hi all!,

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.

I.e:

Captura de Pantalla 2021-05-03 a la(s) 10.44.14.png


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:

Captura de Pantalla 2021-05-03 a la(s) 10.54.22.png

Here is what I expect:


Captura de Pantalla 2021-05-03 a la(s) 10.55.49.png

I'll appreciate any help! Thank you all!

Guillermo Espindola

Best Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @gespindola

    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!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    @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"

Answers

  • Giacomo
    Giacomo Member

    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?

  • gespindola
    gespindola Member
    edited May 2021

    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.

    For example:

    • 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)

    Captura de Pantalla 2021-05-11 a la(s) 10.04.49.png


    Thank you in advance

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @gespindola

    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!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    @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"
  • gespindola
    gespindola Member

    Thank you for your help guys! It worked :) @jaeW_at_Onyx , @GrantSmith

Categories