Historical Trend

icechunk

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.

DavidChurchman

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:

MarkSnodgrass

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

pauljames

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

marcel_luthi

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

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

DavidChurchman

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: