Brainforge Knowledge

wholesale_sortable_solution

5 min read

Fix: Sorting Breaks Array Formula Query

Problem

When you try to sort a sheet populated by ={'Wholesale Customers Data - From Snowflake'!A2:AP}, the sort operation breaks the array formula and the data goes blank.

Why this happens:

  • Array formulas create a single “block” of output
  • Manual sorting tries to rearrange individual cells
  • These two operations conflict, causing the formula to break

Solution: Use QUERY Function (Sortable & Filterable)

Replace the array formula with a QUERY function that supports built-in sorting and filtering.

Step 1: Update the Formula in A2

Replace this:

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

With this:

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

What this does:

  • SELECT * - Pulls all columns
  • WHERE A IS NOT NULL - Filters out empty rows
  • ORDER BY P DESC - Sorts by column P (TOTAL_REVENUE) descending by default
  • 0 at the end - Excludes headers (since you have headers in row 1)

Step 2: Make Sorting Dynamic (Optional)

If you want users to be able to change the sort column, create a helper cell:

In cell Z1 (or any unused cell), add a dropdown:

  • Data → Data validation
  • Criteria: List of items
  • Enter: P DESC, P ASC, O DESC, O ASC, S DESC, S ASC
  • This lets users choose: Revenue (high to low), Revenue (low to high), Orders (high to low), etc.

Then update the formula in A2 to reference the helper:

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

Step 3: Alternative - Use SORT Function (More Flexible)

If you want maximum flexibility, use SORT wrapped around the array:

In A2:

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

Parameters:

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

To make it dynamic, reference a helper cell:

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

Where:

  • Z1 = Column number to sort by (16 for Revenue)
  • Z2 = TRUE/FALSE for ascending/descending

Better Solution: QUERY with Built-in Sorting

The QUERY approach is recommended because:

  • ✅ Supports filtering natively
  • ✅ Can be sorted without breaking
  • ✅ More performant for large datasets
  • ✅ Can add WHERE clauses for filtering

Full QUERY Formula with All Features:

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

To Add Filtering:

You can add WHERE clauses for filtering:

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

Column Reference Guide for QUERY

When using QUERY, columns are referenced by letter:

  • A = Customer ID
  • B = Email
  • C = Full Name
  • D = Phone
  • E = Company Name
  • F = Segment
  • …
  • P = TOTAL_REVENUE (column 16)
  • Q = TOTAL_TAX (column 17)
  • R = AVERAGE_ORDER_VALUE (column 18)
  • O = TOTAL_ORDERS (column 15)
  • W = PARTNER_STATUS (column 23)

To find column number:

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

Option 1: Simple QUERY (Default Sort by Revenue)

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

  1. Users can still use filters:

    • Select row 1
    • Data → Create a filter
    • Filters will work on the QUERY output

  2. To change sort order:

    • Modify the ORDER BY P DESC part
    • Or create helper cells for dynamic sorting (see Step 2 above)

Option 2: QUERY with Helper Cells for Dynamic Sorting

  1. Create helper cells:

    • Z1: Sort Column (enter column letter: P, O, S, etc.)
    • Z2: Sort Direction (enter: DESC or ASC)

  2. In A2:

=QUERY('Wholesale Customers Data - From Snowflake'!A:AP, 
  "SELECT * WHERE A IS NOT NULL ORDER BY " & Z1 & " " & Z2, 
  0)
  1. Users change Z1 and Z2 to sort differently

Testing Checklist

After implementing, test:

  • Sort by Total Revenue (Z to A) - Should work
  • Sort by Total Revenue (A to Z) - Should work
  • Sort by Total Orders - Should work
  • Sort by Last Order Date - Should work
  • Filter by Status (Active/Churned) - Should work
  • Filter by Segment - Should work
  • Filter by Revenue range - Should work
  • Multiple filters at once - Should work
  • Data updates when source refreshes - Should work

Why This Works

QUERY function:

  • Executes the sort as part of the formula
  • Outputs a result set that can be filtered
  • Doesn’t conflict with manual operations
  • More stable than array formulas for sorting

Array formulas (={...}):

  • Create a single output block
  • Can’t be sorted manually without breaking
  • Better for simple data pulls without sorting needs

Quick Fix (Copy-Paste Ready)

For the “Report - All Wholesale Customers” sheet:

  1. Keep headers in row 1 (static, copied from source)

  2. In A2, use this formula:

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

  1. This will:

    • Pull all data from source
    • Filter out empty rows
    • Sort by Total Revenue (high to low) by default
    • Allow filters to work properly
    • Allow sorting to work (by changing ORDER BY clause)

  2. To sort by different columns, change P DESC to:

    • O DESC = Total Orders (high to low)
    • P ASC = Total Revenue (low to high)
    • S DESC = First Order Date (newest first)
    • T DESC = Last Order Date (newest first)
    • W ASC = Status (alphabetical)

Advanced: Multi-Column Sorting

To sort by multiple columns (e.g., Revenue then Orders):

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

This sorts by Revenue first, then by Orders for ties.

Graph View