Using Query function to sum transactions by category and month

Google Sheets
, ,
1

Hello,

I am using Tiller to automatically track my income and expenses into the net worth tracking spreadsheet I use. Unfortunately, the query functions I have tried only give me the results for this month’s (February 2024) and only when the formula calls for January’s expenses.

The two query functions I have tried (given in absolute value for my expenses tab; same result without absolute value function):

Formula 1 (simplified version for testing):
=ABS(QUERY(Transactions!A:I, “SELECT SUM(E) WHERE D = ‘Travel’ AND YEAR(I) = 2024 AND MONTH(I) = 1 LABEL SUM(E) ‘’”))

This gives me $118.20 which is the absolute value of the flight I booked in February. The formula should give me the travel coded expenses for 01/2024 ($30 for checking a bag at the airport). Changing month to 2 for February gives me a N/A error (Query completed with an empty output). I get the same N/A error if I try to do 12/2023. This also happens when trying other categories. I have transactions for these months. All of my transactions have been manually assigned to a category.

Formula 2 (The same thing but for use in my expenses tab which is ordered by months in column A using a M/YYYY format; A17 is 01/2024. This is or a variant would be the formula I would use for actual use.)
=ABS(QUERY(Transactions!D:I, “SELECT SUM(E) WHERE D = ‘Travel’ AND YEAR(I) = " & YEAR(DATEVALUE(“1/” & TEXT(A17, “M/YYYY”))) & " AND MONTH(I) = " & MONTH(DATEVALUE(“1/” & TEXT(A17, “M/YYYY”))) & " LABEL SUM(E) ‘’”))

Same issue.

What is going wrong that only February’s expenses are actually being read by the formula and only when the formula calls for January’s expenses? Thanks for any help anyone can provide (including if anyone has developed a more efficient formula for this, could not find one by searching).

3

Since a fresh Transactions sheet would have the actual transaction Date in Column B, what date is in your Column I that you are using in your query? I would think you want to verify that it is consistent with the actual transaction date or change the query to run off the actual transaction date. For example Date Added is farther right in the sheet, but I would expect it to be lagging and your case is kind of opposite of that where your query is picking up based on some data that is leading the actual date.

4

Thanks for your reply. Column I is “Month” and is formatted as 2/1/24, 1/1/24, 12/1/23, and so on. Google Sheets recognizes it as a date format. A to I is: Tiller logo (A), Date (B), Description (C), Category (D), Amount (E), Account (F), Account # (G), Institution (H), Month (I).

5

Should clarify that I also get February’s results when I query for the month of February (as expected). Querying for January’s results also produces February’s results. Going further back than that though just gives the “Error Query completed with an empty output” despite having plenty of category tagged data from December and November 2023.

6

It might be easier for you to use built in solutions. See if the Transaction Tracker for Google Sheets helps out.

7

Thanks for your help, but unfortunately I’m using Tiller to automate my income and expenses into my larger net worth spreadsheet so I need a way to distill monthly category spend and similar info into single formulas for the larger spreadsheet and its component tabs. I did try to use your Transaction Tracker to see if I could implement my formulas there successfully like I was trying with the default Transactions tab but I ran into the same error I’ve described in the OP.

I do not think the issue is with my spreadsheet tracker, though, as I can recreate the bug in a new Google sheet loaded only with template Tiller tabs (copying over my Transactions and Categories tabs that have only been modified by creating new categories and assigned fill data to those categories). On the default “Spending Trends” tab, fielding “Insights for” last quarter (October 1, 2023 through December 31, 2023) produces this February’s expenses.

8

Another thing to keep in mind is to try to only use the date in column B, the other dates might be off or in a different format.

9

Hey @tilleruser1, you’ve run into one of the many quirks of the Google Sheets query language: months run from 0 (January) to 11 (December). Try rewriting your query for January using MONTH(I) = 0 and for December using MONTH(I) = 11. The result you get using MONTH(I) = 1 will be for February.

In your example where you refer to a date in cell A17, you’ll need to subtract one from the Sheets MONTH() function result in order to align it with the Query month() function result:

=ABS(
  QUERY(
    Transactions!D:I, 
   "select sum(E) 
    where D = 'Travel' 
      and year(I) = " & YEAR(A17) & " 
      and month(I) = " & MONTH(A17)-1 & " 
    label sum(E) '' "))

Ref: Query Language Reference (Version 0.7) | Charts | Google for Developers:

month() Returns the zero-based month value from a date or datetime value. For example: month(date "2009-02-05") returns 1. Note: the months are 0-based, so the function returns 0 for January, 1 for February, etc.

Parameters: One parameter of type date or datetime

Return Type: number

2 Likes
10

That was it exactly. Thanks so much for your help!

1 Like