Pivot Table Slicer Last 3 Years

jstevens
GoldLounger
Posts: 2617
Joined: 26 Jan 2010, 16:31
Location: Southern California

Pivot Table Slicer Last 3 Years

Post by jstevens »

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.
el_Slicer_Year.xlsx
You do not have the required permissions to view the files attached to this post.
Regards,
John

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Pivot Table Slicer Last 3 Years

Post by HansV »

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.
Best wishes,
Hans

jstevens
GoldLounger
Posts: 2617
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Pivot Table Slicer Last 3 Years

Post by jstevens »

Thanks Hans!
Regards,
John

jstevens
GoldLounger
Posts: 2617
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Pivot Table Slicer Last 3 Years

Post by jstevens »

Hans,

The slicer is picking up a "blank" as an option. Anyway to disable it?

EL_103.png

I have attached a modified workbook reflecting the change.
el_Slicer_Year.xlsx
You do not have the required permissions to view the files attached to this post.
Regards,
John

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Pivot Table Slicer Last 3 Years

Post by HansV »

I don't think so.
Best wishes,
Hans

jstevens
GoldLounger
Posts: 2617
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Pivot Table Slicer Last 3 Years

Post by jstevens »

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.

Code: Select all

[Query="Select *#(lf)From myTable#(lf)Where Year(StmtDate) >=Year(GetDate())-3"])
Regards,
John

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Pivot Table Slicer Last 3 Years

Post by HansV »

Nice!
Best wishes,
Hans