🏆 Waterfall Chart

Thanks for sharing! This visualization made me realize I have way too many categories, I’ll need to take some time and consolidate similar ones, but it’s already pretty informative.

1 Like

Thanks! I just updated it to include the ability to view by group, maybe that will work better for those like you that have lots of categories. Delete your sheet and re-copy and you should be set.

@randy, I was playing with the inline VLOOKUP, but I think you’re right, could have bogged things down a bit. I instead pre-staged the data on the side and now the dropdown for Category/Group determines which range is displayed for the chart. Working well for me, let me know if it works for you!

Awesome stuff! Got it set up and noticed that categories I’ve selected to “Hide From Reports” are included in the chart. How can we exclude hidden categories?

Right now the only way is to add them to the Transfer group, but it makes sense that ‘hidden’ items should be hidden here too. I’ll try to add that soon!

I made some big changes. Instead of hiding the ‘Transfer’ group and categories, I changed it to hide the ‘Hide From Reports’ categories, to behave more like the other Tiller reports. I think I originally made this sheet too customizable. Since users shouldn’t be changing sheet names and column names that would otherwise break the template, I’m now assuming those original names are retained, so I eliminated the settings to allow changing them, which simplifies the sheet and makes the formulas easier to read. Now, the only ‘setting’ is the description of the paycheck transactions. If that isn’t added, paycheck periods won’t show up in the ‘Periods’ dropdown. I’ve updated the original post to reflect the current behavior and features. Hopefully everyone feels this is moving forward, not backward. Copy the new version of the template and let me know if you have any issues, or any other ideas for improvements!

If I can offer one more suggestion… :slight_smile:
I added in B5 and B6, Net Cashflow and then the sum of b9:b that way I can see right away where I am and don’t need to scroll, especially if I have lots of groups.

I’m not too financially savvy, what is ‘Net Cashflow’ and how does it differ from the sum of B9:B?

That’s what I meant, sorry for being unclear.
Net Cashflow is the header field and below it is the formula.

That seems like useful info, I’ve added it into the template, and did a little rearranging. Thanks for the suggestion!

Thanks for all your work on this. The Waterfall tab is a great addition to my Tiller Spreadheet!

2 Likes

Wow, this is great! The best part, for me anyway, is that it’s using actuals not budgeted. I can finally see where I am during the month in terms of my income and spending.

Nice work!

1 Like

Hide From Reports categories still show up in the categories & group summation and graph.

Strange, they don’t show up on mine. If you unhide the columns on the right side, does W12 properly identify your ‘Hide From Reports’ column on the Categories sheet?

Yes, It identifies it as “G” which is the column in categories.

I can’t tell what would cause that then, I would probably have to look at a copy of your spreadsheet to figure out what’s gone wrong.

Could it be because some of the categories start with a number? eg 8-Insurance instead of Insurance? All the hidden categories with letters, such as transfers are ok while categories with number-letter hidden were not.

You could try replacing Y3 with this formula instead, it uses TEXTJOIN instead of JOIN:
=QUERY(Transactions!A:Z, "select "&W7&",SUM("&W8&") Where "&W9&" >= date '"&TEXT(B6,"yyyy-mm-dd")&"' and "&W9&" <= date '"&TEXT(B7,"yyyy-mm-dd")&"' and not "&W7&" matches '"&TEXTJOIN("|",true,QUERY(Categories!A:Z, "select "&W10&" Where "&W12&" = 'Hide'"))&"' GROUP BY "&W7&" ORDER BY SUM("&W8&") DESC LABEL "&W7&" 'Category', SUM("&W8&") 'Amount'")

Thanks but did not resolve the issue.

Another suggestion…
A9 should have iferror before the if statement just to make things nicer, especially in the beginning of the month.

-edit-
Also, just an FYI, the previous payperiod doesn’t work well with the data copied from the paycheck template. I have the description set to my company name, so I have multiple for that day, (gross/net/tax, etc.) so my start date is 1/21 and my end date is 1/20 (1/21 -1) but that is my current payperiod.

What you need to do is make sure you are selecting Gross pay in the description field, that way you will be filtering correctly.

Thanks, I added the IfError. You’re right, I hadn’t accounted for using the same description for multiple transactions. So wouldn’t you want to differentiate the paycheck transactions by description, or are you just using the categories to differentiate them? I could both look for the ‘Paycheck Description’, as well as a positive value, seems that should find the gross pay.