When the Waterfall just doesn’t line up right

Pipeline waterfall charts are great to analyze your pipeline. Recently, Salesforce launched Einstein Analytics, and it comes with these amazing charts. However, some Salesforce Orgs will face some “bugs” with these charts. Bars on these charts won’t even out, and the following equation can be false:

Open Pipe (Start) + Reopen + Expand + Moved In – Moved Out – Reduce – Closed Won – Closed Lost = Open Pipe (End).

In this post, I’ll explain why this equation can fail and how to fix it.

Find out inconsistencies with Excel

If the equation above is false, it’s because 1 or more variables are wrong. By clicking on each bar, you’ll see the details below. I created one Excel table per variable with all opportunity details and used VLOOKUPS in order to see which opportunities were missing, and where.

By doing so, I found out that some opportunities from the Open Pipe (Start) variable didn’t appear in any of the other variables, and explained why my equation was wrong.

Next, I tried to understand in which bucket these opportunities were supposed to fall into. I opened some records and understood that they all needed to fall into the “Moved In” bucket, because the closed dates were changed.

The Dashboard Inspector

Why were these opportunities not considered as Moved In opportunities? This step was trickier as I needed to dig into the code of the pre-built Sales Analytics app.  The Dashboard Inspector is the way to check the code. So I opened the Team Trending dashboard and opened the Dashboard Inspector.

Bucket values come from the waterfall_chart step, so I opened its details.

Then, I tried to understand how the 4_MOVED_IN variable was computed.

Here’s the code we’re interesting in:

when ( not ‘New’ and toDate(‘CloseDateEnd’) in [“current quarter”..”current quarter”] and ((‘Stage.IsClosedStart’ == “false” and not(toDate(‘CloseDateStart’) in [“current quarter”..”current quarter”])) or (‘Stage.IsClosedStart’ == “true”)) ) then “4_MOVED_IN”

Here are the variables:

• CloseDateEnd
• Stage.IsClosedStart
• CloseDateStart

Among the variables listed above, I knew that CloseDateEnd and CloseDateStart were correct as the close date were indeed “Moved In” to the period I selected in Sales Analytics app. Hence, Stage.IsClosedStart was the source of my problem. Why?

The Pipeline Trending Dataset

The Team Trending dashboard comes from the Pipeline Trending Dataset.

If you open the Sales Analytics dataflow, and go to the Register_PipelineTrending node, you’ll see that the field “Stage.IsClosed” comes from the Extract_OpportunityStage node.

The solution

If you have deleted some obsolete opportunity stages from the setup menu, these stages don’t have any IsClosed attribute, and the Company Trending dashboards won’t work as expected.

Here are the steps to follow to solve this issue:

1. Export the Opportunity History object with the Data Loader
2. Create a Pivot Table to retrieve all unique obsolete stages
3. Re-create all obsolete stages you’ve deleted in the past through the setup menu
4. Deactivate all stages you’ve just created
5. Never delete them ever again

In this way, the field “Stage.IsClosed” from the Extract_OpportunityStage node in the Sales Analytics Dataflow for your obsolete stage names won’t be blank, but false.

Hence, the 4_MOVED_IN variable below will work for all your opportunities, and your waterfall charts will be accurate.

when ( not ‘New’ and toDate(‘CloseDateEnd’) in [“current quarter”..”current quarter”] and ((‘Stage.IsClosedStart’ == “false” and not(toDate(‘CloseDateStart’) in [“current quarter”..”current quarter”])) or (‘Stage.IsClosedStart’ == “true”)) ) then “4_MOVED_IN”