Change sort on the Transaction Description Sum By Category on the Cat Tracker

Google Sheets
1

I’d like to change the sort by Amount instead of alphabetical in the Cat Tracker Report.
I tried changing the ORDER BY in the following formula to &I3& but that failed.

=iferror(QUERY(Transactions!A:AE,“SELECT " &I1& " , SUM(” &I3 & “) WHERE " &I4& " >= DATE “”” & text(E4, “yyyy-mm-dd”) & “”" AND " &I4& " <= DATE “”" & text(E5, “yyyy-mm-dd”) & “”" AND " &I2& " = ‘" & E3 & "’ GROUP BY " &I1& " ORDER BY " &I1& " ASC LABEL SUM(" &I3 & “) ‘’, " &I1& " ‘’”, -1),“”)

Any tips for getting me started, I’m trying to figure out the items in the hidden columns but the light bulb has not turned on yet

2

@bkmccarty Check out this conversation… it may help.

3

Thanks but I saw that conversation. It speaks to the formula in column A. I’m talking about the formula in Column D7

4

Would love to see solution here, I think my category tracker sheet is different than the topics covered here. I have attempted to sort by amount vs category by changing ORDER BY Col1 to ORDER BY Col 3, but this does not work. BTW, Col1 refers to Category and Col3 refers to amount. Can the developer create new logic where the option to sort by category or amount is in the feature set?

5

Figured it out, The column is really called sum(Col3), so when you change the ORDER BY clause from ORDER BY Col1 to Col3 it needs to read ORDER BY Sum(Col3)

6

I am interested in changing the order at which the data is displayed from alpha numeric to by highest to lowest amount. I see above this has already been asked and answered but it seems like the sheet has changed from the last time this was asked. If I’m in the correct location, the formula is in cell A12 and is:
=IFERROR(SORT(FILTER($L$12:$L,N12:N<>"Hide",L12:L<>"")))

what do I change here to get the data to sort from highest to lowest amount?

7

The SORT() function is documented here.

So it may be as simple as adding a FALSE to the is_ascending field. You could experiment with something like this:
=IFERROR(SORT(FILTER($L$12:$L,N12:N<>“Hide”,L12:L<>“”)), FALSE)

8

unfortunately when I added the FALSE into A12 nothing happened. I wanted to make sure you understood my question thought…I’m trying to change the order from alpha numeric in the “category” column to highest to lowest expense under the “Amount” category.

What cell should I be making the change in and what is the change?

9

Maybe this then?
=IFERROR(index(SORT(FILTER({$L$12:$L,$M$12:$M},N12:N<>"Hide",L12:L<>""),2,false),,1))

10

thank you very much…what you provided worked, I just needed to change false to true and it ordered it so I could see my biggest expense drivers on top vice my biggest income drivers.

11

Wow, very nice. This worked. Now I can see the expenses from high amount to low. Much appreciated!
Now, any idea how I can do the same on the Monthly Budget tab?

12

Thanks for the idea here. I created a data validation dropdown box in B10 with the following sort order choices: “Category”, “Income First” and “Expense First”. Then I put the following formula in cell A12:

=if(B10=“Category”,IFERROR(SORT(FILTER($L$12:$L,N12:N<>“Hide”,L12:L<>“”))),if(B10=“Income First”,IFERROR(index(SORT(FILTER({$L$12:$L,$M$12:$M},N12:N<>“Hide”,L12:L<>“”),2,false),1)),IFERROR(index(SORT(FILTER({$L$12:$L,$M$12:$M},N12:N<>“Hide”,L12:L<>“”),2,true),1))))

This allows me to select which of the three sort orders I want from the dropdown list.