Transaction Tracker does not filter when Type dropdown has a type selected

Conversation Ask Anything
1

In Transaction Tracker I want to see all expenses for a particular time period. But, when I select Type: “Expenses” and a time “Period” I get all transactions - not filtered by Type.
I have checked my Categories sheet and verified all Categories have the correct type assigned.

1 Like
2

@SBRocks I see there is a note in the solution instructions about this.

Please note that the “Type” filter does not filter the transactions. It is only used to help in the filtering of the filters. Selecting a Type filter, will narrow down the Cat/Group filter.
The exclude filters, will filter from the results the specific account or category. So if you filter by institution (group), your account (category) drop down will have all accounts (categories) for that institution (group). You can then select a specific item to exclude without having to run the report for each specific one.

2 Likes
3

@SBRocks

This was something I wanted as well and just recently updated my Account and Category Report to include this and other filter and grouping options. You can get it here- 🏆 Account and Category Report.

Thanks.

1 Like
4

If you’re willing to modify the formulas, consider making these changes :slight_smile:

Google Sheets
In cell A12 (note that this includes some other refinements I’ve been using):

=QUERY(
 {INDIRECT(V14),INDIRECT(V15),
  if(isna(T19),arrayformula(XLOOKUP(INDIRECT(V18),INDIRECT(V16),INDIRECT(V17),"")),INDIRECT(V19)),
  INDIRECT(V18),INDIRECT(V20),INDIRECT(V21),INDIRECT(V19),INDIRECT(V22),INDIRECT(V24),INDIRECT(V26),
  arrayformula(XLOOKUP(INDIRECT(V18),INDIRECT(V16),INDIRECT(V23),""))},
 "select Col1, Col2, Col3, Col4, Col10, Col9, Col5, Col6, Col8 
  where " & 
    if(D6="","","upper(Col2) contains """&UPPER(D6)&""" and ") & 
    if(D5="","","Col9 ="""&D5&"""  and ") & 
    if(D4="","","Col10 = """&D4&""" and ") & 
    if(H4="","","Col9 != """&H4&""" and ") & 
    if(H5="","","Col4 != """&H5&""" and ") & 
    if(B4="","","Col11 = """&B4&""" and ") & 
    if(B5="","","Col3 = """&B5&""" and ") & 
    if(B6="","","Col4 = """&B6&""" and ") & 
    if(F6="","","Col6 contains """&F6&""" and ") &  
   "Col1 >= date '"&TEXT(AC13,"yyyy-mm-dd")&"' and Col1 <= date '"&TEXT(AC14,"yyyy-mm-dd") & 
    if(H11=FALSE,"'","' and NOT Col4 matches """ & TEXTJOIN("|",true,AA13:AA) & """") & 
    if(F4=FALSE,""," and Col8 <> """" ") &
    if(F5="",""," and upper(Col8) contains """ & UPPER(F5) & """ ") & 
    if(D7 ="Negative","and Col5 <0 ","") & 
    if(D7 ="Positive","and Col5 >0 ","") & 
    if(H8 ="","","and Col5 <= "&H8&"") & 
    if(H7 ="","","and Col5 >= "&H7&"") & 
    if(F7 ="","","and Col5 = "&F7&"") & 
 "Order by Col1 "&H6&", Col9 asc 
  Label Col1 'Date', Col2 'Description', Col3 'Group', Col4 'Category', Col5 'Amount', Col6 'Tags', Col8 'Note', Col9 'Account', Col10 'Institution'")

In cell V28 (adds Type filtering to Category dropdown):

=if(B5="",
  if(B4="",
    unique(sort(filter(INDIRECT(V16),(INDIRECT(V16)<>"")))),
    unique(sort(filter(indirect(V16),indirect(V23)=B4)))
  ),
  unique(sort(filter(indirect(V16),indirect(V17)=B5)))
)