Written by:Akash Duseja (Deactivated)

The following example shows how to implement a Revenue Waterfall Dashboard. It compares the year 2016 to 2015 but we can configure it to compare Quarters, Months, Weeks, Days etc.

The different components (from left to right) include:

Technical details of the dashboard creation:

  1. Drop-down selections: Use DatamartContext.dimFilterEntry("Name", Column) to get the drop-downs in the dashboard.
  2. Create tables for time periods in question using SelectQuery(..computed columns..).where(api.newDatamartSlice("DateColumnName", period)) for both periods. In our example, we obtain columns like Revenue, Quantity and PricePerUnit from these tables.
  3. Read revenues from Time Periods just by obtaining SUM(Revenue) from the tables created in Step 2.
  4. To get Lost Business, we use "SELECT T1.* FROM T1 LEFT OUTER JOIN T2 ON T1.Product=T2.Product AND T1.Customer=T2.Customer WHERE T2.Product IS NULL" where T1 is the comparison period and T2 is the current period (or the more recent of the two). Select Revenue from the result to get the Lost Business Amount.
  5. To get New Business, we use "SELECT T2.* FROM T1 RIGHT OUTER JOIN T2 ON T1.Product=T2.Product AND T1.Customer=T2.Customer WHERE T1.Product IS NULL" where T1 is the comparison period and T2 is the current period (or the more recent of the two). Select Revenue from the result to get the New Business Amount.
  6. Next, we fetch the business that does not fall in lost or new business category and we will call it common business. The easiest way to get that data would be to use the query "SELECT * FROM T1 INNER JOIN T2 ON T1.Product=T2.Product AND T1.Customer=T2.Customer".
  7. To get the Volume Effect, we iterate through common business data and compute ((T2.Volume - T1.Volume) / T1.Volume) to get the fraction increase in Volume and multiply that by T1.Revenue to get the volume effect for that row of data in the common business. Add that up over all the rows and we will have the total Volume Effect.
  8. To get Price Effect, iterate through common business data and compute (T2.PricePerUnit – T1.PricePerUnit) and multiply that by T2.Volume. That will give us the change in revenue due to change in Price Per Unit for each row of common business. Now add this up for all the rows to compute the total Price Effect.
  9. To calculate the Portfolio Mix Effect, iterate through common business data and compute (T1.PricePerUnit) * (T2.Volume/SUM(T2.Volume) - T1.Volume/SUM(T1.Volume)) for every row. Multiplying the sum of these computed values to the SUM(T2.Volume) will give us the total Portfolio Mix Effect.
  10. Plotting all of these computed items using a custom waterfall template will allow you to create a dashboard that will look similar to the example shown here.