Pivot Table not refreshing properly

[mikeymay]

I have a PT based on a table of data that contains the following fields/columns
* Run - This is the name of a delivery route
* Week Comm - This is the week com date in which the Run will occur

The Run field is used as a filter for the PT rather than being 'in' the PT

First problem is that in the PT filter drop-down the values are not in alphabetical order

The option for Custom lists in the totals & Filters option is set to off

The when I click on the drop-down for the list of each Run, the list shows values that don't exist in the source data

Specifically, the GATE run is not in the source date. It may have been previously and removed, but as of now it does not exist.

Below is a screen shot of the source date with a filter open showing all unique Run entries

As you can see GATE is not there.

Second problem is when I make a selection of a Run in the PT filter for example SWEST (which does exist in the source date), the PT shows rows from the source date that do not have the SWEST entry in the Run column.

So the PT is showing filter options that don't exit and when an option that does exit is selected, it returns entirely incorrect data.

FYI - The Data option for the number of items to retain is set to None

TIA of any advice anyone can provide.

 

[RoryA]

I'd start by making sure the source data for the pivot table is actually what you think it is, then make sure the data is refreshed. Beyond that there really isn't a lot to say without the file.

 

[mikeymay]

I'd start by making sure the source data for the pivot table is actually what you think it is, then make sure the data is refreshed. Beyond that there really isn't a lot to say without the file.

100% positive the source data is correct and the PT is linked to that data.

Unfortunately I can't share the file as it is a clients file with confidential sales data.......

I am going to rebuild the PT and keep an eye on it. It will get flagged quickly if it happens again but fingers crossed it won't......