Is it possible to have a Pivot Slicer for year to automatically show the current and prior 3 years? I don't need the slicer to show historical years beyond the 3 prior years.
I have attached a sample workbook.
Pivot Table Slicer Last 3 Years
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Pivot Table Slicer Last 3 Years
You do not have the required permissions to view the files attached to this post.
Regards,
John
John
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Pivot Table Slicer Last 3 Years
There is no direct support for that.
You could add a calculated column to the data source that replaces all years before the current year minus three with a blank, and use that column for your slicer.
You could add a calculated column to the data source that replaces all years before the current year minus three with a blank, and use that column for your slicer.
Best wishes,
Hans
Hans
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Pivot Table Slicer Last 3 Years
Hans,
The slicer is picking up a "blank" as an option. Anyway to disable it?
I have attached a modified workbook reflecting the change.
The slicer is picking up a "blank" as an option. Anyway to disable it?
I have attached a modified workbook reflecting the change.
You do not have the required permissions to view the files attached to this post.
Regards,
John
John
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Pivot Table Slicer Last 3 Years
Hans,
I was able to restrict the Slicer to the current year and three years back by creating a new connection to an external database. The new connection contains a SQL to restrict the data being retrieved.
No need for helper columns.
I was able to restrict the Slicer to the current year and three years back by creating a new connection to an external database. The new connection contains a SQL to restrict the data being retrieved.
No need for helper columns.
Code: Select all
[Query="Select *#(lf)From myTable#(lf)Where Year(StmtDate) >=Year(GetDate())-3"])
Regards,
John
John
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands