Brainforge Knowledge

wholesale_working_solution

4 min read

Working Solution: Filters That Don’t Break

The Real Problem

Google Sheets regular filters don’t work with array formulas or QUERY outputs. When you change a filter, it tries to hide/show rows in a formula output, which breaks everything.

Solution: Use Filter Views (Not Regular Filters)

Filter Views work differently - they’re saved views that don’t conflict with formulas.

Step 1: Update Formula (Use Simple Array Formula)

In A2 of “Report - All Wholesale Customers”:

={'Wholesale Customers Data - From Snowflake'!A2:AP}

OR if you want default sorting:

=SORT({'Wholesale Customers Data - From Snowflake'!A2:AP}, 16, FALSE)

(16 = Revenue column, FALSE = descending)

Step 2: Remove Regular Filters

  1. If you have regular filters enabled, remove them:
    • Select row 1
    • Data → Turn off filter (or click the filter icon)

Step 3: Create Filter Views Instead

  1. Data → Filter views → Create new filter view
  2. Name it: “High Revenue Customers” or “Active Only” etc.
  3. Apply filters:
    • Click filter dropdown on any column
    • Set your criteria
    • Sort by Revenue (Z→A) or any column
  4. Save the view

Benefits:

  • ✅ Works with array formulas
  • ✅ Works with QUERY
  • ✅ Multiple users can have different views
  • ✅ Views are saved and can be shared
  • ✅ No conflicts with formulas

Step 4: Create Multiple Filter Views

Create different views for common use cases:

  • “All Customers - Revenue High to Low”
  • “Active Customers Only”
  • “High Value (>$10K Revenue)”
  • “At Risk (>180 days since order)“

Alternative: Two-Sheet Approach (Most Reliable)

If Filter Views don’t work for your use case, use this:

Sheet 1: “Data - Live Pull”

  • Purpose: Just pulls data, no filtering
  • In A1: ={'Wholesale Customers Data - From Snowflake'!A:AP}
  • No filters, no sorting - just raw data

Sheet 2: “Report - All Wholesale Customers”

  • Purpose: User-facing report with filters
  • Row 1: Copy headers manually (static)
  • Row 2: ='Data - Live Pull'!A2:AP
  • This is NOT an array formula - it’s a direct reference
  • Add regular filters here - they will work!

Why this works:

  • The array formula is in Sheet 1 (hidden/background)
  • Sheet 2 just references it (not an array formula)
  • Regular filters work on references, not array formulas

Quick Fix to Try Right Now

Option A: Switch to Filter Views

  1. Remove regular filters (if any)
  2. Data → Filter views → Create new filter view
  3. Apply your filters and sorting in the view
  4. Save it

Option B: Use Two-Sheet Approach

  1. Create new sheet: “Data - Live Pull”
  2. In A1 of that sheet: ={'Wholesale Customers Data - From Snowflake'!A:AP}
  3. In “Report - All Wholesale Customers” sheet:
    • Row 1: Headers (static)
    • Row 2: ='Data - Live Pull'!A2:AP
  4. Add regular filters - they should work now

Why Regular Filters Break

Regular filters:

  • Try to hide/show rows in formula outputs
  • Conflict with array formulas
  • Conflict with QUERY outputs
  • Cause formulas to recalculate incorrectly

Filter Views:

  • Are saved “views” of the data
  • Don’t modify the underlying data
  • Work with any formula type
  • Can be shared between users

For “Report - All Wholesale Customers” Sheet:

  1. Formula in A2:
=SORT({'Wholesale Customers Data - From Snowflake'!A2:AP}, 16, FALSE)
  • Defaults to Revenue high-to-low
  • Can be changed by modifying the 16 (column) and FALSE (direction)

  1. Use Filter Views for filtering:

    • Data → Filter views → Create new filter view
    • Apply filters and sorting
    • Save multiple views for different use cases

  2. To change default sort:

    • Edit the SORT formula
    • Change 16 to different column number
    • Change FALSE to TRUE for ascending

Column Numbers for SORT Function

When using SORT(array, column_number, ascending):

  • 16 = Column P (TOTAL_REVENUE)
  • 15 = Column O (TOTAL_ORDERS)
  • 17 = Column Q (TOTAL_TAX)
  • 18 = Column R (AVERAGE_ORDER_VALUE)
  • 19 = Column S (FIRST_ORDER_DATE)
  • 20 = Column T (LAST_ORDER_DATE)
  • 23 = Column W (PARTNER_STATUS)

To find column number:

  • Count from A: A=1, B=2, C=3… P=16, Q=17, etc.

Testing Checklist

After implementing:

  • Data loads correctly
  • Filter Views can be created
  • Filter Views allow sorting
  • Filter Views allow filtering
  • Multiple Filter Views can coexist
  • Data updates when source refreshes
  • No blank data when changing filters

If Still Not Working

Try the two-sheet approach - it’s the most reliable:

  1. Background sheet with array formula
  2. Report sheet that references it (not array formula)
  3. Regular filters work on the reference

This is the most compatible solution with Google Sheets filtering.

Graph View