Sorting Pivot Table based on Jer Report

Joshua Hammond

April 09, 2019 19:49

None

Hello,

I am trying to sort a pivot table on multiple fields. I used the Inventory Availability report created by Jet, and altered it slightly to include a couple of additional fields.

When I use the sort function nothing changes in the pivot table. I am using the following function:

=NL("Table","27 Item",$D$11:$X$11,"Headers=",$D$10:$X$10,"TableName=","Item","Filters=",$B$7:$C$7,"InclusiveLink=27 Item",$D$9,"InclusiveLink=27 Item",$F$9,"IncludeDuplicates=","True")

I tried adding in the "+" or "-" on the fields but it would not sort the report table or the corresponding pivot table.

My goal is to have three levels of sorting - 1st by Vendor No. 2nd by Available to ship - which is a calculated field, and 3rd by Qty. on Purch. Order.

Thanks,

Joshua

Date Votes

1 comment

1

Harry Lewis April 09, 2019 20:52

Hello Joshua -

The NL(Table) function sorts data based on the order in which the fields appear in the table.

In this example:

The results are sorted by the No. field, then the Description, then Quantity on Hand, etc. because that is the order in which those fields appear in this table.

By changing the order of my fields (using Table Builder), I've changed the view:

Excel's Pivot capability does not care in what order my data appears in the table. That Excel feature imposes its own sorting.

So, regardless of the order in which my data appears, the Excel pivot tables are identical:

We can override that sorting default, however.

For example, let's suppose I want both Item Category Code and Product Group Code to be shown in DESCENDING order.

I would first click, in the Pivot table, on an Item Category Code and then I can use the Sort capability on the Data ribbon:

That would re-sort my Pivot table:

I would then repeat the process with the Product Group Code:

Now both are in DESCENDING order:

I hope that gets you pointed in the right direction.

Comment actions Permalink

Please sign in to leave a comment.