Excel pivot table using date column to filter results

Question

I have a Pivot Table which I have entered a date field into the Page Fields area to filter the data in the page.

However, I only have the option to select individual dates.

I want to use a date range. How can I do this? Or Can I do this?

Which Excel version? Are you accessing external data (e. g. from Analysis Services or a relational source), or is the source in the same Excel file? — Frank
– Frank, Commented Jun 30, 2010 at 12:13

Raj More · Accepted Answer · 2010-06-30 18:51:16Z

2

Here is a classification by data source of how you can get / use the

Analysis Services

If you are using Analysis Services data, then I think you a breakdown of a date structure (if the dimension and the field is classified as such) by default.

(Hidden) Excel Sheet Data Source

Date Structure breakdowns are not readily available for data from another (usually hidden) sheet in the workbook.

You will have to create additional columns that mark ranges and then you can use it.

For example, lets say you have a SalesDate column. In your source, you should break the column further down so you get the the following columns

SalesDate
SalesYear
SalesQuarter
SalesMonth
SalesWeek

Then you can use these to effectively create ranges. If you have custom ranges you can put them here as well.

External SQL Query

If you are using a SQL Query, you can create computed columns that do these calculations for you. If you have the ability to modify the SQL Query, you can add the columns to the query.

In case you don't have the ability to modify the SQL, you can create Calculated field in the Pivot Table structure.

edited Jun 30, 2010 at 18:51

answered Jun 30, 2010 at 12:18

Raj More

48.2k34 gold badges139 silver badges199 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ktdrv Over a year ago

Good solution but this would only work is the data comes from an Excel range. If the source is external, you are stuck unless you can go to the source and have the data modified there.

Raj More Over a year ago

@kaloyan not necessarily stuck. you can always add a calculated field.

ktdrv Over a year ago

Depends on the data source. If it's OLAP, you can't. Speaking from experience, when your data source is external, you can't add a calculated field more often than you can.

Collectives™ on Stack Overflow

Excel pivot table using date column to filter results

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related