0

I have a spreadsheet of financial transactions. The Columns headers are (A) Date, (B) Category, (C) Subcategory, (D) Description, (E) Amount.

I have the following query that creates a pivot table from that data that lists financial transaction categories in one column, and the MONTH(A)+1 to calculate how much is spent each month per category across the next 12 columns:

=query(A1:E, "SELECT B, SUM(E) WHERE A is not null GROUP BY B PIVOT MONTH(A)+1",1)

This works well enough, but the column headers are 0 - 11 for the months. I need for them to actually be January, February, etc. Or, better yet, January 2021, February 2021, etc. Column A is formatted as an actual date as YYYY-MM-DD.

How can I modify the query to accomplish that?

(related question: How to merge multiple functions to dynamically create a single table of data?)

1 Answer 1

0

Leave column A as dates but create an array of all the dates changed to the first of the month using the EOMONTH function.

EOMONTH(date, -1)+1 will return the first day of the same month as in date.

Now when you use those values in your pivot, QUERY also returns date values that you can format as "MMMM YYYY" or as you prefer.

=QUERY({INDEX(IFERROR(EOMONTH(A:A,-1)+1)), B:E},
   "SELECT Col2, SUM(Col5) WHERE Col1 IS NOT Null 
    GROUP BY Col2 PIVOT Col1", 1)

You could optionally format the dates within the formula instead of using a date format in the sheet. The results of that approach would not be date values, but text strings, which may not matter to you.

=QUERY({INDEX(IFERROR(TEXT(EOMONTH(A:A,-1)+1, "MMMM YYYY"))), B:E},
   "SELECT Col2, SUM(Col5) WHERE Col1 IS NOT Null 
    GROUP BY Col2 PIVOT Col1", 1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.