Waterfall chart with annual values at each end and variances as waterfall steps

We had to set this up via a data flow.  But we have accomplished a graph similar to this.  We were looking at a rolling 6 month vs 6 month variance in our waterfall.

Here are the steps we used to set up the graph:

1.) Create a table called current_6_months

SELECT
a.`Account Name`
,CASE WHEN `Salesforce Account ID` IS NULL THEN '(Not Assigned)' ELSE `Salesforce Account ID` END AS `Salesforce Account ID`
,CASE WHEN a.`Product Family` IS NULL THEN '(Not Assigned)' ELSE a.`Product Family` END AS `Product Family`
,CASE WHEN a.`Product Group` IS NULL THEN '(Not Assigned)' ELSE a.`Product Group` END AS `Product Group`
,SUM(a.`Sales at constant Exchange Rate`) `Sales`

FROM sales_with_account_segment_and_rollup a

WHERE a.`Date of Invoice` >= DATE_SUB(CURDATE(),INTERVAL 6 MONTH)
GROUP BY
a.`Account Name`
,CASE WHEN `Salesforce Account ID` IS NULL THEN 'Undefined' ELSE `Salesforce Account ID` END
,CASE WHEN a.`Product Family` IS NULL THEN '(Not Assigned)' ELSE a.`Product Family` END
,CASE WHEN a.`Product Group` IS NULL THEN '(Not Assigned)' ELSE a.`Product Group` END

2.) Create a table called prior_6_months

SELECT
a.`Account Name`
,CASE WHEN `Salesforce Account ID` IS NULL THEN 'Undefined' ELSE `Salesforce Account ID` END AS `Salesforce Account ID`
,CASE WHEN a.`Product Family` IS NULL THEN '(Not Assigned)' ELSE a.`Product Family` END AS `Product Family`
,CASE WHEN a.`Product Group` IS NULL THEN '(Not Assigned)' ELSE a.`Product Group` END AS `Product Group`
,SUM(a.`Sales at constant Exchange Rate`) `Sales`

FROM sales_with_account_segment_and_rollup a

WHERE a.`Date of Invoice` >= DATE_SUB(CURDATE(),INTERVAL 12 MONTH)
AND a.`Date of Invoice` < DATE_SUB(CURDATE(),INTERVAL 6 MONTH)

GROUP BY
a.`Account Name`
,CASE WHEN `Salesforce Account ID` IS NULL THEN 'Undefined' ELSE `Salesforce Account ID` END
,CASE WHEN a.`Product Family` IS NULL THEN '(Not Assigned)' ELSE a.`Product Family` END
,CASE WHEN a.`Product Group` IS NULL THEN '(Not Assigned)' ELSE a.`Product Group` END

3.) Union the two tables (we called this full_union)

SELECT
`Account Name`
,`Salesforce Account ID`
,`Product Family`
,`Product Group`
,SUM(Sales_Cur_6_Month) Sales_Cur_6_Month
,SUM(Sales_Prior_6_Month) Sales_Prior_6_Month

FROM (
SELECT
`Account Name`
,`Salesforce Account ID`
,`Product Family`
,`Product Group`
,`Sales` Sales_Cur_6_Month
, 0 Sales_Prior_6_Month
FROM current_6_months

UNION ALL

SELECT
`Account Name`
,`Salesforce Account ID`
,`Product Family`
,`Product Group`
,0 Sales_Cur_6_Month
,`Sales` Sales_Prior_6_Month
FROM prior_6_months
) a
GROUP BY
`Account Name`
,`Salesforce Account ID`
,`Product Family`
,`Product Group`

4.) Identify New Vs Lost business (we labeled this table new_business)

SELECT
`Account Name`
,`Salesforce Account ID`
,`Product Family`
,SUM(Sales_Prior_6_Month) Sales_Prior_6_Month
,SUM(Sales_Cur_6_Month) Sales_Cur_6_Month
,CASE
WHEN SUM(Sales_Cur_6_Month) > SUM(Sales_Prior_6_Month) AND SUM(Sales_Prior_6_Month) = 0 THEN 'New Business'
WHEN SUM(Sales_Cur_6_Month) < SUM(Sales_Prior_6_Month) AND SUM(Sales_Cur_6_Month) = 0 THEN 'Lost Business'
END `New vs Lost Business`

FROM full_union

GROUP BY
`Account Name`
,`Salesforce Account ID`
,`Product Family`

5.) Create different Scenarios that you want to track with waterfall (create_scenarios)

SELECT
a.*
,CASE
WHEN b.`New vs Lost Business` IS NOT NULL THEN b.`New vs Lost Business`
WHEN a.Sales_Cur_6_Month > a.Sales_Prior_6_Month AND a.Sales_Prior_6_Month = 0 THEN 'Menu Expansion'
WHEN a.Sales_Cur_6_Month <= a.Sales_Prior_6_Month AND a.Sales_Cur_6_Month = 0 THEN 'Menu Attrition'
WHEN a.Sales_Cur_6_Month > a.Sales_Prior_6_Month THEN 'Organic Growth'
WHEN a.Sales_Cur_6_Month <= a.Sales_Prior_6_Month THEN 'Organic Attrition'
END AS Scenario
,a.Sales_Cur_6_Month - a.Sales_Prior_6_Month as `Amount Change from Prior 6 Months`

FROM full_union a
LEFT JOIN new_business b
ON a.`Account Name` = b.`Account Name`
AND a.`Salesforce Account ID` = b.`Salesforce Account ID`
AND a.`Product Family` = b.`Product Family`

WHERE CASE WHEN IFNULL(a.Sales_Cur_6_Month,0) = 0 AND IFNULL(a.Sales_Prior_6_Month,0) = 0 THEN 'T' ELSE 'F' END = 'F'

ORDER BY `Account Name`, `Product Family`

6.) Union Growth and Attrition (union_growth_and_attrition)

SELECT
`Account Name`
,`Salesforce Account ID`
,`Product Family`
,`Product Group`
,`Scenario`
,`Amount Change from Prior 6 Months` as `Waterfall Amount`
,`Amount Change from Prior 6 Months`
,Sales_Cur_6_Month
,Sales_Prior_6_Month

FROM create_scenarios

UNION ALL

SELECT
`Account Name`
,`Salesforce Account ID`
,`Product Family`
,`Product Group`
,'12-6 Months' `Scenario`
,`Sales` as `Waterfall Amount`
,0 `Amount Change from Prior 6 Months`
,0 Sales_Cur_6_Month
,0 Sales_Prior_6_Month

FROM prior_6_months

This will give you a data set that you can use to create your waterfall.  

We created two beastmodes to help with the graph:

"Amt | Waterfall" was used at the VALUE field:

SUM(CASE
WHEN `Scenario` != 'Last 6 Months'
THEN `Waterfall Amount`
END)

"Waterfall Sort" was used for SORTING:

CASE
WHEN `Scenario` = '12-6 Months' THEN 1
WHEN `Scenario` = 'Organic Growth' THEN 2
WHEN `Scenario` = 'Menu Expansion' THEN 3
WHEN `Scenario` = 'New Business' THEN 4
WHEN `Scenario` = 'Lost Business' THEN 5
WHEN `Scenario` = 'Menu Attrition' THEN 6
WHEN `Scenario` = 'Organic Attrition' THEN 7
ELSE 8
END

You then use `Scenario` as the SUMMARY GROUP and `Scenario` as ITEM NAMES