Apple Numbers: Filtering pivot table for top/bottom 20 items

I don't have your data, so can't tell, but it may also be worth considering that the tables are built based on the cell underlying value, not the display value. That means that the Pivot Table would consider $100.00 and $100.00001 as different values even though they both display as $100.00. If you have fractional holdings or any kind of rounding on the values, that could affect the results.

Second, as a completely hokey workaround, if you absolutely want 20 rows then you could add one level of indirection. Have your pivot tables as you do now, with any number of rows, and create a separate table of 20 rows (plus headers, etc.) that directly reference the top 20 values from the pivot table. That way, even if the pivot table shows 25, 30, or even 100 values, your indirect table only references the first 20 rows. Hokey, like I said, but sometimes that's what we have to resort to. :)

It's easier to do "top n" in Excel, if I remember correctly.

In Numbers you could try sorting on the relevant column.

Then in Pivot Options on the right switch to the Filter tab and select the top 20 manually.

Not fully automatic but it should get the job done.

SG

Oh, it looks like Numbers is trying to be too smart with its references to the pivot table.

I vaguely recall seeing this before, but it wasn't critical to my workflow, so I ignored it.

The problem is that when you built the second-level table and linked it to a cell in the pivot table, Numbers internally referenced the underlying value of that cell.

When you update the pivot table, it adjusts all those links to maintain references to that specific data not the specific cell row/column in the table. If the stock that was in A3 is now no longer listed, the reference is invalid and Numbers (conveniently?) removes it from the lookup. That's the root of why there's a gap in the lookup range.

I guess in some world that makes sense. Just not in yours :)

All is not lost, though. You can break that relationship via the INDIRECT() function, and it's actually a little easier.

In the Top 20 table, replace the INDEX() functions with something like:

=INDIRECT("Pivot: Perf by Ticker, Top 20::$A$3").

This will grab the actual value from cell A3, without trying to track it through the pivot shenanigans as it moves up/down (in/out?) of the table.

I vaguely remember coming across this before. If I remember rightly, it has something to do with unique values.

When you ask for 'top 20', Numbers sorts the top 20 values, then shows all cells that match those values. If you have multiple cells that have the same value, they're all included because it's showing the top 20 values, not the top 20 cells sorted by values.

I don't recall the specifics, but there was some workaround that I'll have to dig out, but I don't think I was using pivot tables at the time, so that may be an issue.

If you want to force 20 rows then you may have to resort to a static table, not a pivot table.

RustyWiring wrote:

I’ve submitted a request to Apple (yeah, right) to make pivots update automatically.

I doubt whether you (or I) would be happy with the results of that.

Pivot Tables take a lot of processing power and would slow down the interface if they constantly updated.

For that reason, both Excel and Numbers require you to "refresh" them, which is just one click.

SG

I agree INDEX() should be viable here.

How does this formula differ from other formulas in the table that are working?

The oddity is that it seems to be creating two ranges, skipping row 3 in the table.

If the pivot is drawing the right results then you might also just directly reference the cell (=Pivot: Perf by Ticker, Top 20::$A$3)

> That yields an invalid reference error.

You did it wrong ;)

It looks like you entered INDIRECT() then provided a link to the cell. That's not how it should be set.

You, effectively, type out the reference to the cell that you want. Note my example is a quoted string, including the table name and column/row numbers. INDIRECT() decodes the string in real time to work out where to go look for data.