Help with pivot

[Ofina]

I have a spreadsheet that has the following:
Invoice No, Invoice Date, Customer, Supplier, Total Price, Total Supplier Cost

I am making a customer x supplier pivot with invoice date in the filter section so that my users can select appropriate date ranges. The date filter drop down in the pivot, however, doesn't show up the way I would like it. It gives me options like this:

02/16/14
03/12/14
05/23/14

And what I would like is this:

+ 2013
+ 2014
+ 2015

with the option to drill into those years.

The autofilter drop down on the spreadsheet does this. How can I get the pivot to do the same thing?

Also, less urgent is the ability to choose Price or Cost to be down in the pivot. Maybe as another drop down in the filters section?

 

[SkipVought]

Hi,

It would be helpful to post the source data that supports your example.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

I mocked up some data.

Have you looked at the Date Filters in your pivot?

If you want to use the stock AutoFilter that you refer to, then make your own pivot using Data > Unique values and appropriate aggregation formulas.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Ofina]

There are no date filters available for the filter section of the pivot. Unless I'm looking in the wrong spot.

Can you further explain your last sentence? I think you are suggesting that I just create a new tab with a bunch of formulae (if statements?) based off of the raw data. Is there a way to do that, assuming that is what you are suggesting, and keep a date filter drop down at the top the same way that a pivot does?

I gather from your answers that there is no way to do what I want directly.

 

[Ofina]

I got closer to what I want by bringing the invoice date into the row section, grouping it by year there, and then dragging it back up to the filter section again.
This has allowed me to select specific years easily, but not open up the years to drilling into more day-detail the way the regular autofilter does.

Though it might be good enough.

 

[combo]

If you could duplicate invoice date column, one column can be grouped by years and used as page field.

combo

 

[Ofina]

Where would the other one go?

 

[combo]

As page field, but first as row field for grouping (by years) and next dragged to page field

combo