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:
DatamartContext.dimFilterEntry("Name", Column)
to get the drop-downs in the dashboard.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.SUM(Revenue)
from the tables created in Step 2.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.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.SELECT * FROM T1 INNER JOIN T2 ON T1.Product=T2.Product AND T1.Customer=T2.Customer
".((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.(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.(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.