Comparisons Report Error

[Lillypad]

I downloaded the most recent version of the Google Sheets version of the “Comparisons report” found here:

Docs: Comparisons report

What is the Comparison Report? An easy way to better understand your spending is by comparing it across two time periods. The time periods could be a week, month, quarter, or year. When your income or spending changes from one period to the next, you can gain a better understanding of what happened by looking at which categories or groups changed the most and least. Our financial comparison report is designed to help you gain those insights. The sheet compares your spending or earnings across…

However, I am seeing an mistake in the data reported with this version. This might be a bug in the latest version based on my digging, but thought I would run this by all of you for a sanity check.

When viewing the report, the data always drops one Category or one Group from the results. When I expand the hidden columns to the right of the report, I noticed that each column header (in the hidden area) has rows 1 and 2 merged. The formulas are then entered into the “merged” top row of the columns with formulas. It seems that having these cells merged is causing the report to drop the first Category or Group in the results.

Part of the reason I’m thinking that is the issue is because the original version of the Comparisons Report does not have any merged cells in the hidden data.

Has anyone else seen this and fixed it?

[Mark.S]

I just recently installed the Comparisons Report, so I don’t have much experience with it yet so far, and I haven’t seeing the issue you’ve described.

But it does remind me of an issue that can occur when using the QUERY function, where if QUERY is allowed to guess how many header rows there are, that can cause a row of data to get swallowed. As an example, QUERY might guess there are two header rows when there is really just one and so the first row of data is considered part of the header rows, and doesn’t get included with the rest of the data.

There are some QUERY functions being used in this sheet and so as an experiment you could try changing the queries that either use -1 or nothing for the header row argument. I found 3 occurrences of that, shown below with the change to 1 header row.

Cell A8
OLD:

=if(counta(Y3:Y)>0,query(Y:AC,"SELECT Y, Z, AA, AB WHERE Y IS NOT NULL AND AC <> 'Hide' ORDER BY AA LABEL Y '',Z '', AA '', AB '' ",-1),iferror(1/0))

NEW:

=if(counta(Y3:Y)>0,query(Y:AC,"SELECT Y, Z, AA, AB WHERE Y IS NOT NULL AND AC <> 'Hide' ORDER BY AA LABEL Y '',Z '', AA '', AB '' ",1),iferror(1/0))

Cell S1:
OLD:

=iferror(query(L:M,"SELECT M, SUM(L) WHERE M IS NOT NULL GROUP BY M LABEL SUM(L) 'Sum'"))

NEW:

=iferror(query(L:M,"SELECT M, SUM(L) WHERE M IS NOT NULL GROUP BY M LABEL SUM(L) 'Sum'",1))

Cell V1:
OLD:

=iferror(query(P:Q,"SELECT Q, SUM(P) WHERE Q IS NOT NULL GROUP BY Q LABEL SUM(P) 'Sum'"))

NEW:

=iferror(query(P:Q,"SELECT Q, SUM(P) WHERE Q IS NOT NULL GROUP BY Q LABEL SUM(P) 'Sum'",1))

[ninneman]

I just discovered the same problem, so I’m feeling some relief to find your post. It drops the first alphabetical CATEGORY for me. (GROUP was already working correctly before the change) It did not correct it after applying the suggestion below (which makes sense since the S and V columns are for Group processing. I noticed that the suggested addition of “,1” to cell S1 and V1 was already in place on the columns processing the categories (using K1 and O1), so nothing to change there. I even tried removing “,1” in K1 and O1 on a whim.

Did you get it to work? Thank you.

[Mark.S]

I see it now too. Yeah, it’s the query function inside the merged cell that’s causing the problem. And it’s not as simple as unmerging the cell, because other formulas are looking for the data to start in row 3.

@randy @heather This is a real bug in this template.

[ninneman]

The fix:

unmerge cells in G1/G2: AC1/AC2

change the formulas to use row 2 not row 3. Needed in cells M2, Q2, Y2, Z2, AA2, AB2, AC2

change cell A8 formula to begin at Y2 not Y3

[randy]

Finally broke away from other projects and peeked at this today.
Thanks for flagging this and tagging me, @Mark.S.

I can definitely reproduce it and think I have a fix in version 2.11. (I appreciate your detailed fix, @ninneman, those are helpful, but the cell letters you had didn’t quite line up for me even though the approach is similar. I wonder if you had an older version of the template.)

Let me know if it works for you all.
Thanks for your patience as always.

[ninneman]

Thanks for your responsiveness. I think there is some ungrouping and/or including of row 2 yet to be done at the top of columns M, S, T (for the group, not category, report).

[Mark.S]

@randy

but the cell letters you had didn’t quite line up for me even though the approach is similar

I just think his instructions didn’t include that he moved some formulas from row three to row two.

[randy]

Ok, @ninneman & @Mark.S… Thanks for checking my work. You are right that it was incomplete. Just pushed a new version 2.12 which should be live soon.