• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot table field showing items that have been filtered out

In my raw data, I have a list of names and dates (January through July). I created a column to extract the month from the dates column. In the "filters" box of the pivot table window, I have the months column, with January and February filtered out.

In the "rows" box of the pivot table window, I have the date field. When I select the pull down arrow on the date field, I see all dates from January through July. I was hoping to see March through July.

I tried PivotTable Options > Data > Number of items to return per field = None then I refreshed the pivot table and this still didn't work.

I would like the date field to show dates only from March to July (exclude January and February). How can I make this happen?
 

Chihiro

Excel Ninja
If it's Filter field and row field... these two are not directly linked.

Row label date filter will have all possible dates (including dates that have no data) from earliest to latest date (depending on grouping applied this behaviour will change slightly).

Date field is special case, as it's treated as linear data and not categorical data (i.e. data is contiguous range from start to finish, and not collection of distinct values).

If Date field is grouped by only Months (i.e. date is not present in Pivot Rows Field), then it becomes categorical.

To control dates, it's recommended that you use Timeline slicer or some other means.
 
Top