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

Padraic

I'm trying to create a waterfall chart, where the first bar in the waterfall is the absolute value for 2017, and the steps in the waterfall are the variances in each category.  This should result in the summary bar being the absolute value for 2018.

 

My best solution so far is to create a new category for values in 2017, however, this means that the values in 2017 are unavailable for the variance calculation for each category.

 

Has anyone done this previously, or is there another way to create the 2017 bar at the start of the waterfall?

 

I've illustrated this problem in the attached excel file.  Columns B, C and D are my dataset, and E to J are my beast mode columns/calculations.  I have 6 categories A to F, and I create a new category G for 2017 values, so that G is the first bar in my waterfall, with an absolute value for 2017.  However, to calculate the variance of categories A to F, for the steps in the waterfall, the rows in 2017 are now in the G category, and unavailable to categories A to F.

ST_Superman

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

 

ST_Superman

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

 

Padraic

I'm not sure if I followed your logic exactly, but I was able to solve this issue with an ETL dataflow. I filtered my data into 2017 and 2018, then split the 2017 data into two copies, where one copy is used for the 2017 bar of the waterfall, and the second copy is combined with the 2018 data to calculate the variances of each item.

Unknown

I was trying to do the same thing and found this post.  It worked as advertised.  It was pretty easy to adapt to my use case.  For anyone interested in finding New business, Lost business, Expansion, and Contraction of sales over a period of time this is a useful example.  Thanks for sharing

swagner

@ST_-Superman-_  I wanted to let you know I found this thread, followed your example, and was able to implement a Customer Churn page in my instance of Domo based on what you provided here.

 

HUGE THANK YOU!

ST_Superman

Good stuff, glad I could help.