Waterfall Chart

What is the goal of your workflow? What problem does it solve, or how does it help you?
I use a ‘Waterfall’ chart to show category or group totals for selectable time periods, and make how expenditures fit within income more apparent.

How did you come up with the idea for your workflow?
My wife doesn’t understand numbers. To help her understand where our money is going, I need to provide visuals. Pie charts and Bar charts are OK for comparing categories to each other, but I wanted to include how expenditures fit within earnings. While figuring out how to do that, I stumbled across the ‘Waterfall’ chart type, included in Google Sheets. A waterfall chart shows how values add or subtract from a starting value. When referencing a sorted query, the chart uses vertical bars showing how your income was generated, and then a cascade of increasingly larger subtractions for each category or group, with a subtotal at the end showing the difference between your income and expenses.

Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?
Everything exists within one sheet that you can copy to your spreadsheet. The features of this sheet work out of box for Foundation Template users.

Anything else you’d like people to know?
You can change the period of time you’re seeing transactions from using the ‘Period’ dropdown. You can change if you’re viewing by category or group using the ‘View By’ dropdown.
If you enter the description of your paycheck transactions in S4 (either the entire description or a unique portion), you’ll see additional paycheck options in the Period dropdown. Note that the ‘Paycheck’ options will be most useful for those with a single major pay source.

  • Current Paycheck - Enter your paycheck ‘Description’ in S4. Period starts on the date of the first transaction with a description that contains S4, and extends until now.
  • Previous Paycheck - Enter your paycheck ‘Description’ in S4. Period starts on the date of the second transaction with a description that contains S4, and extends until the day before the first transaction with a description that contains S4.
  • Since Last Sunday - Period starts on the most recent Sunday and extends until now.
  • Since Start of Month - Period starts on the first day of the current month and extends until now.
  • Previous Month - Period starts on the first day of last month and extends until the last day of last month.
  • Since Start of Year - Period starts on the first day of the current year and extends until now.
  • Previous Year - Period starts on the first day of last year and extends until the last day of last year.
  • Number of Days - After selecting this period, B4 will become visible so you can enter a number. The period starts the number of days you entered before now, and extends until now.
  • All - Period starts on the date of your oldest transaction and extends until now.

Is it ok for others to copy, use, and modify your workflow?
Yes, please, and please provide feedback on any issues you encounter and/or suggestions on how to make improvements!

If you said yes above, please make a copy of your workflow and share the copy’s URL:

I love this @jpfieber. It was kind of eye-opening for me. I added it to my sheet, entered previous year and saw that we lost money— which wasn’t a surprise. What was uncanny though was that we were within a few dollars of breakeven until a significant tax expenditure (the final bar on the right).

Out of the box, I had it working in under a minute.

I especially like the way you built the column header finders with REGEXEXTRACT(). It’s a cleaner approach than the one I’ve been using forever.

Really nicely done overall.

Would you be willing to add a Tiller Community badge? The intent is to communicate that the solution is built for compatibility with Tiller’s data structure and will protect user privacy. Most of the formerly-Tiller-Money-Labs templates have been updated to include the logo and a “Learn & Discuss” link to help content. You can DM me if you have any questions or feedback on this idea…

image

1 Like

Thanks, glad you like it and that it worked well for you. I spent a lot of time converting it from a ‘just for me’ to something that should work for most users, and that process actually helped me come up with a number of improvements that made it better than what I started with.

I can’t take credit for the REGEXTRACT header finder, I copied that from the Detailed Category Tracker, so I think it’s @cculber2 we have to thank. I agree that it is quite excellent and should probably be base code for most community templates!

I did plan on adding the badge after I was sure it actually worked for someone other than me. If you can let me know here or in DM the details of size, preferred URLs, etc I’m happy to add it.

3 Likes

I’m afraid I can’t take credit for the usage of REGEXTRACT(), which was original to Tiller’s Category Tracker sheet. :sweat_smile:

Sadly, that process of abstracting your creations to make them work seamlessly in a foreign sheet is often the most time consuming part of the sharing process. I know how you feel… but I’m glad to hear there were some improvements that it unlocked for you, @jpfieber.

Regarding the badge, I’d just load one of the newly updated templates in TCS, @jpfieber, and copy those two cells over with their formatting (and update the community URL). We don’t have firm guidelines (yet) regarding exactly how it all goes together. Thanks for being the first!!! :medal_sports:

That Category Tracker template is OLD! I think that must be a @tom or @jono original. I always try to use the latest and greatest formulas from past creations, but somewhere along the line that REGEXTRACT() was lost until you unearthed it, @cculber2. :bone:

Thanks again for sharing!
Randy

1 Like

Thanks for this, this is really helpful. I hope I can ask for a modification, I think I can do it myself but not sure if I want to edit the SQL and break something.

My paycheck description isn’t always the same, the last few characters is the date. Is there a way for a “starts with” instead of “match” in the SQL?

I updated the template to use ‘contains’ instead of ‘=’, for that comparison, which should work for you. Remove your old sheet and copy the template again and let me know if that does it!

1 Like

Looks great, @jpfieber! Thanks for being the first to try the Tiller Community badge.

1 Like

Thanks for the help, looks like we’re getting somewhere. :slight_smile:
Current Paycheck works, but previous gives an error on the end date.

-edit- Nevermind, just edited the “end date” formula to “contains” and it now works.

Woops, my bad, forgot I needed to update that formula as well. Updated now, try copying again.

1 Like

This is really great and insightful! One thing I did modify was to add a drop down to select, “Category,Group” and then the row in Column T changes to that text so I can view the waterfall at a higher level if needed.

Love it! Way to go @jpfieber - so cool to see a great share of a cool visualization you’ve built. I know this will be useful to others.

Great idea! I’ll play with adding that functionality into the template.

One thing to keep in mind if you do that is that you’ll need to remove the Transfer Type group from the SQL as well, so I added a simple and not "&U11&" = 'Transfer Types' and the switch is simply pointing to the drop down, “=A5” which is either Category or Group.
For reference, here is my query, (note that some of the fields were moved down so may not fully match your columns): =QUERY(INDIRECT(V3), "select "&U11&",SUM("&U12&") Where "&U13&" >= date '"&TEXT(A8,"yyyy-mm-dd")&"' and "&U13&" <= date '"&TEXT(B8,"yyyy-mm-dd")&"' and not "&U11&" matches '"&JOIN("|",QUERY(INDIRECT(V4), "select "&U14&" Where "&U15&" = '"&T7&"'"))&"' and not "&U11&" = 'Transfer Types' GROUP BY "&U11&" ORDER BY SUM("&U12&") DESC LABEL "&U11&" '"&T11&"', SUM("&U12&") 'Amount'")
Screenshot 2021-09-15 151803

I’ve been working on how to add the ability to view by group, but I’m realizing it only works if you have a ‘Group’ column in your Transactions sheet, otherwise you’d probably have to do a vlookup for the category of each transaction to get the group. I have the Group column, and it works great, but the default template does not. I was hoping to keep this template completely self reliant, but it looks like adding this feature would require adding a column and formula to the Transactions sheet. Thoughts?

I think you are right. I have the column in my sheet due to an add on. No idea which one, but it’s a simple lookup from the category to get group. This is the formula I have:

=ARRAYFORMULA(IFERROR(VLOOKUP(D:D,Categories!A:B,2,FALSE),IFERROR(1/0)))

I actually think this is one from @peter :slight_smile: it was in our “Tiller Standard template” for the longest time before there was anything else.

1 Like

I think you are right not to assume or require that the user has added a Group column to the Transactions sheet.

As for adding the groups, that is relatively easy to bolt onto the left side of the existing output (with an ARRAYFORMULA() containing a VLOOKKUP() to the Categories sheet).

What isn’t easy is using the Group as a sort-driving field. Essentially, you’d need to have the group lookup already performed before you render the output that drives the chart. There would be two ways to do this:

  • Create a hidden area to the right of the chart where you stage the data, perform the group lookup, and then query the complete set of fields for sorting at the left (for the chart). This is easy but is a bit messy and incremental.
  • Nest all of the above into a single complex QUERY()… clean but fussy and hard to debug.
  • Conceivably you could perform the VLOOKUP in real-time via a dynamic range into the existing QUERY but then you’d be doing the lookup for all (thousands?) transaction rows which could affect performance.

On a separate note, @jpfieber, thanks for adding the community badge. The intent on the Learn & Discuss hyperlink is to link to content specific to the solution (i.e. this page). Could you change the link to be this formula?

=HYPERLINK("https://community.tillerhq.com/t/waterfall-chart/9731","Learn & Discuss")

Updated the link. Thanks for the thoughts on getting groups to work. I’d like this template to be self reliant, so I’m going to continue working on getting groups working without reliance on the Groups column being in Transactions. An interesting puzzle!

This is great. I tried it in my Foundation Template and shared it on Twitter. Love a good waterfall chart! (Even if mine isn’t looking so hot right now :grimacing:)