Historical Trend

icechunk
icechunk Member
edited October 2023 in Magic ETL

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

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    I would suggest a re-structure of the data to something like this:

    image.png

    Then you could do a waterfall:

    image.png

    Or a running total bar, which sounds more literally what you're asking for:

    image.png

    Here's the MagicETL I used to restructure the data:

    image.png

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    On your initial dataset, you can it to append instead of replace, which will allow you to build a historical trend.

    image.png

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • pauljames
    pauljames Contributor

    @icechunk , here you go. Here is some test data i created. I'm guessing you just have a file of sorts that looks like this. Is this what you are looking for?

    image.png
    image.png
    image.png

    IF I SOLVED YOUR PROBLEM, PLEASE "ACCEPT" MY ANSWER AS A SOLUTION. THANK YOU!

  • marcel_luthi
    marcel_luthi Coach

    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.

  • icechunk
    icechunk Member
    edited October 2023

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

    Screenshot 2023-10-31 at 10.31.35 AM.png

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    I would suggest a re-structure of the data to something like this:

    image.png

    Then you could do a waterfall:

    image.png

    Or a running total bar, which sounds more literally what you're asking for:

    image.png

    Here's the MagicETL I used to restructure the data:

    image.png

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Categories