Hi all,
We need help creating a waterfall of our Pipeline. Based on 2 selected dates, we would like to see what happened to our deals. Which did we win, which did we lose, which changes values and which new deals have been added.
If we take the following data as example:
| Deal ID | Date | Status | Value | WHAT HAPPENED? |
| 1 | 01-01-2020 | Open | 100 | Open |
| 2 | 01-01-2020 | Open | 100 | Open |
| 3 | 01-01-2020 | Open | 100 | Open |
| 4 | 01-01-2020 | Open | 100 | Open |
| 5 | 01-01-2020 | Open | 100 | Open |
| 6 | 01-01-2020 | Open | 100 | Open |
| 1 | 02-01-2020 | Open | 100 | Still Open |
| 2 | 02-01-2020 | Open | 100 | Still Open |
| 3 | 02-01-2020 | Won | 100 | Won |
| 4 | 02-01-2020 | Lost | 100 | Lost |
| 5 | 02-01-2020 | Open | 80 | Change |
| 6 | 02-01-2020 | Open | 100 | Still Open |
| 7 | 02-01-2020 | Open | 100 | New Deal - Open |
Let's compare 01-01-2020 to 02-01-2020.
The first pillar of the Waterfall should always be "Value of OPEN deals" of the "starting date".
For the above example, we have 6 deals Open on 01-01-2020 worth a total of 600 in Value.
The Last pillar (ending point) of the Waterfall should always be the "Value of OPEN deals" of the "Ending Date".
For the above example, we have 5 deals Open on 02-01-2020 worth a total of 480 in Value.
The Values in between the starting end end dates has to be the following:
WON: Sum of deals that was Open at starting date, but is now Won at ending date. (also New deals added in between, that is already Won too). Should be negative number.
LOST: Sum of deals that was Open at starting date, but is now Lost at ending date. (also New deals added in between, that is already Lost too). Should be negative number.
CHANGE: Sum of "the change" of deals that was Open at starting date, and still Open at ending date.
NEW: Sum of deals that was not existing at starting date, but is now existing.
Using the above example:
Starting Date:
STARTING DATE OPEN: 600
WON: -100
LOST: -100
CHANGE: -20
NEW: 100
ENDING DATE OPEN: 480
I hope this is possible!
On top of this, we would like to be able to "Click" on for instance the "Won" pillar, and see which deals this affects!
Thanks!!
