Issue After Updating Monthly Analysis Template to Version 1.02

Hi,

I just updated this Lab from version 1.0 to 1.02 and have come across an odd bug.

  • Whatever the current timerange is set to, the value for budget of the last one (so if last 12 months, August; if 2020 - december) is inaccurate (usually, but not always 0; if positive valued, not complete)
  • All other budget fields are correctly added
  • No observed issues with the actual expense field
  • It seems to be that specific, last cell. When on “last 12 months”, August is 0. When on “2020”, August is no longer the last record of the list, and is now correctly calculated. December is then wrong.

The error is most visible in the chart, but the table to the left (Col A-D) also surfaces the error. I inspected the cells in the hidden sheet and it’s clearly not calculating correctly there - but too complicated for me to dig into.

In case you have questions on my workflow:

  • I haven’t modified this sheet before and am on a very vanilla Tiller - i.e. really only added Labs, no customisation myself
  • I have been budgeting from the “Categories” sheet

ok, think I just found the issue and can confirm it’s part of the update itself.

I noticed that the issue was only when using the filter to “All” (cell b7)

For whomever maintains the versioning, this should be an easy update:

  • Take a look at cell X13
  • Note how in the switch function for “All”, it’s referencing the same column as used for the first date in the range
    • i.e. it’s incorrectly putting the budget from month 1 of the period
      • if set to 12 month. 8/2020 displays budget for 9/2019 (which was 0 for me)
      • if set to 2020, 12/2020 displays 1/2020

Recommend reviewing some of the neighboring formula, I did not check extensively

Thanks @andhess. We’ll have the Labs team look into this.

Hi @andhess,
Thanks for alerting us to this issue and helping us identify it.
You are correct. That complex formula in X13 was pointing to the wrong column when All is selected in B7.

We have updated the sheet to Version 1.03 which corrects this issue. It should be available shortly via the Tiller Labs add-on.

For now, you can also update the formula in X13 to:
=SWITCH($B$7,"All",(CB$3*$W$16)+(CB$4*$W$17),"Category",VLOOKUP($B$8,{INDIRECT($U$3),INDIRECT($R13)},2,FALSE),"Group",VLOOKUP($B$8&$B$6,{arrayformula($BO$6:$BO&$BP$6:$BP),CB$6:CB},2,FALSE))

Thanks,
Jon

cc @heather

2 Likes

Great - thank you for the quick fix!

1 Like

Thank you @jono!

1 Like

FYI - The new V1.03, which fixes this problem, is now available via the Tiller Money Labs add-on. Use the Manage Solutions link to update your sheet. Or Add a Solution to install the Monthly Analysis sheet for the first time.

cc @andhess