Brainforge Knowledge

wholesale_filter_fix

5 min read

Fix: Filters Empty Data When Changed

Problem

When using QUERY or array formulas with Google Sheets native filters, changing filter criteria causes the data to disappear.

Why this happens:

  • QUERY outputs are formula results, not actual data
  • Google Sheets filters try to hide/show rows on formula outputs
  • This creates a conflict that breaks the display

Solution: Use Simple Array Formula + SORT (No QUERY)

The key is to use a simple array formula that Google Sheets filters can work with, and handle sorting differently.

Step 1: Use Simple Array Formula (No ORDER BY)

In A2, use this simple formula:

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

Important: Don’t use QUERY. Just use the simple array formula.

Step 2: Handle Sorting with SORT Function

If you want default sorting, wrap it in SORT:

In A2:

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

Where:

  • 16 = Column P (TOTAL_REVENUE) - 16th column
  • FALSE = Descending (TRUE for ascending)

But wait: This still might conflict with filters. Let’s try a different approach.

Better Solution: Two-Sheet Approach

Create an intermediate sheet that just holds the raw data, then reference that:

  1. Create sheet: “Data - Raw Copy”

    • In A1: ={'Wholesale Customers Data - From Snowflake'!A:AP}
    • This is just a simple copy, no sorting

  2. In “Report - All Wholesale Customers” sheet:

    • Row 1: Copy headers manually (static)
    • Row 2: ='Data - Raw Copy'!A2:AP
    • This creates a direct reference that filters can work with

Why this works:

  • The intermediate sheet has the array formula
  • The report sheet just references it (not an array formula)
  • Filters work on the reference, not the array formula

Best Solution: Use IMPORTRANGE-Style Reference

Actually, the simplest fix is to make sure the formula in the report sheet is NOT an array formula, but individual cell references.

Option 2: Individual Cell References (Most Compatible)

In A2:

='Wholesale Customers Data - From Snowflake'!A2

Then copy this formula across all columns (A through AP) and down all rows.

But this is tedious. Let’s use a better approach.

Step 1: Create the Data Pull

In A2 of “Report - All Wholesale Customers”:

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

Step 2: Add a Helper Column for Sorting

Add a new column (let’s say column AQ, after all data):

In AQ1 (header): Sort Helper

In AQ2:

=P2

This just copies the revenue value. Copy this down.

Step 3: Use Filters, Not Manual Sorting

Instead of trying to sort the array formula:

  1. Add filters: Select row 1 → Data → Create a filter
  2. Use filter dropdowns to sort by any column
  3. The filters will work because they operate on the displayed values, not the formula

To sort by Revenue:

  • Click the filter dropdown on the Revenue column
  • Choose “Sort Z → A” or “Sort A → Z”
  • This should work without breaking

Alternative: Use QUERY Without Filters

If you want sorting built-in, use QUERY but don’t use Google Sheets filters:

In A2:

=QUERY('Wholesale Customers Data - From Snowflake'!A:AP, 
  "SELECT * 
   WHERE A IS NOT NULL 
   ORDER BY P DESC", 
  0)

Then create filter views instead of regular filters:

  • Data → Filter views → Create new filter view
  • Filter views work better with QUERY outputs

Best Fix: Remove Array Formula, Use Direct References

The most reliable solution is to not use array formulas at all in the filtered sheet.

Method: Copy-Paste Values (Not Live)

  1. Create a script or manual process to copy data
  2. Paste as values in the report sheet
  3. Filters and sorting work perfectly

But this loses the “live” connection.

Ultimate Solution: QUERY with Filter Views Only

In A2:

=QUERY('Wholesale Customers Data - From Snowflake'!A:AP, 
  "SELECT * 
   WHERE A IS NOT NULL 
   ORDER BY P DESC", 
  0)

Then:

  1. Remove regular filters (if any)
  2. Use Filter Views instead:
    • Data → Filter views → Create new filter view
    • Filter views work with QUERY outputs
    • Multiple users can have different filter views

To change sort order:

  • Modify the ORDER BY P DESC in the formula
  • Or create multiple sheets with different ORDER BY clauses

Quick Test: Try This Formula

In A2, try this version:

=ARRAYFORMULA('Wholesale Customers Data - From Snowflake'!A2:AP)

Instead of ={...}, use ARRAYFORMULA(...). Sometimes this works better with filters.

If Nothing Works: Two-Step Process

  1. Sheet 1: “Data - Live”

    • Formula: ={'Wholesale Customers Data - From Snowflake'!A:AP}
    • No filters, no sorting - just raw data

  2. Sheet 2: “Report - All Wholesale Customers”

    • Use IMPORTRANGE or direct cell references
    • Or copy-paste special → Paste values only (loses live connection)
    • Apply filters and sorting here

Try this in A2:

=ARRAYFORMULA(IF('Wholesale Customers Data - From Snowflake'!A2:A="", "", 'Wholesale Customers Data - From Snowflake'!A2:AP))

This uses ARRAYFORMULA with an IF check, which sometimes works better with filters.

If that doesn’t work, the two-sheet approach is most reliable:

  1. Intermediate sheet with array formula
  2. Report sheet references the intermediate sheet (not array formula)

Graph View