Control/sync multiple slicer timelines from one slicer timeline

[baseline9]

Hi

I have two pivot tables and I want to be able to control date ranges in those pivot tables with a timeline slicer. The problem is I want to control them based upon different dates (Different fields in the raw data). So I want to select 01/01/2016 based upon date1 in one pivot and 01/01/2016 based upon date2 in another pivot.

I can have 2 slicers but the user will only want to click one. I think the solution would be is to have one master timeline slicer showing and then one in the background/hidden that also changes when the master timeline slicer is changed. I think this could be possible via VBA?

Once I have done this for two dates then my aim is two extend it out to more

I really hope someone can help

I'm on XL2013

Many thanks!

 

[Roderick_E]

I realize this is a very old post but I've been trying to accomplish something like this too. I think it can be accomplished by having one slicer timeline based on date1 pivot table and then a macro in date1 pivot table that changes date2 pivot table. Will try a few tests and report back

 

[Roderick_E]

Ok, using the controlling slicer in conjunction with the Worksheet_Change where the controlling slicer changes a pivot, you can force another dataset's slicer to change to the same timerange as the controlling slicer.

Sub alltimelines()
Dim fromdate As Variant
Dim todate As Variant
'controlling timeline
fromdate = ActiveWorkbook.SlicerCaches("NativeTimeline_Dates").TimelineState.FilterValue1
todate = ActiveWorkbook.SlicerCaches("NativeTimeline_Dates").TimelineState.FilterValue2
'shadow timeline(s)
ActiveWorkbook.SlicerCaches("NativeTimeline_Dates[B][COLOR=#ff0000]1[/COLOR][/B]").TimelineState.SetFilterDateRange fromdate, todate
End Sub

Then have Worksheet change trigger the alltimelines code...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Call alltimelines
End If
End Sub

Typically the enduser would not see the shadow timeline, it changes behind the scenes.

 

[jeremy_norbury]

It's entirely unclear to be why, but in order to MAKE it work, I had to change the definition of the fromdate and todate from Variant to Date.

Before I did that, calls to SetFilterDateRange fromdate, todate were failing with XlFilterStatusInvalidDate.

 

[Roderick_E]

Strange. What version of Excel are you using?

 

[jeremy_norbury]

Odd, right?

I've got both 2016 pro and a Office 365 subscription - both latest versions.


I then thought "Why the hell would the dates that come back from here - not be acceptable as parameters?"

fromdate = ActiveWorkbook.SlicerCaches("NativeTimeline_Dates").TimelineState.FilterValue1
todate = ActiveWorkbook.SlicerCaches("NativeTimeline_Dates").TimelineState.FilterValue2

I discovered it by attempting it by using values I knew to be date types from a couple of cells:

fromdate = this sheet.range("myFromDate").value
todate = etc
ActiveWorkbook.SlicerCaches("My timeline name").TimelineState.SetFilterDateRange fromdate, todate

And that worked.

Changed your Variants to Dates and even the original code started working.

 

[Roderick_E]

Well thanks for improving on it. It's been helpful in my work where we often have multiple datasets.

 

[ExcelFren]

Hello!

I'm pretty new to VBA and would greatly appreciate if anyone could help me with this please. I'm trying to link 2 slicers from 2 different date fields, I've tried to follow the codes above but met with Run-time error 1004 (Application-defined or object-defined error). Here's the code used:

Sub AllTimelines()
Dim FromDate As Date
Dim ToDate As Date

'controlling timeline
FromDate = ActiveWorkbook.SlicerCaches("NativeTimeline_Date_of_Appt").TimelineState.FilterValue1
ToDate = ActiveWorkbook.SlicerCaches("NativeTimeline_Date_of_Appt").TimelineState.FilterValue2

'shadow timeline(s)
ActiveWorkbook.SlicerCaches("NativeTimeline_Last_Day_of_Service").TimelineState.SetFilterDateRange FromDate, ToDate

End Sub

Thanks in advance!!

 

[Roderick_E]

Hello!

I'm pretty new to VBA and would greatly appreciate if anyone could help me with this please. I'm trying to link 2 slicers from 2 different date fields, I've tried to follow the codes above but met with Run-time error 1004 (Application-defined or object-defined error). Here's the code used:

Sub AllTimelines()
Dim FromDate As Date
Dim ToDate As Date

'controlling timeline
FromDate = ActiveWorkbook.SlicerCaches("NativeTimeline_Date_of_Appt").TimelineState.FilterValue1
ToDate = ActiveWorkbook.SlicerCaches("NativeTimeline_Date_of_Appt").TimelineState.FilterValue2

'shadow timeline(s)
ActiveWorkbook.SlicerCaches("NativeTimeline_Last_Day_of_Service").TimelineState.SetFilterDateRange FromDate, ToDate

End Sub

Thanks in advance!!

Hi there, my first suspicion is that the names of your timelines are different, thus you need to modify every place in the code that refers to "NativeTime_Date_of_Appt" Go into your FORMULA/NAME MANAGER and look for all timeline names. For example, I have one called "NativeTimeline_FilterDate". Yours is probably different. Then fix the vba code to reference your timeline names. Hope this helps.

 

[ExcelFren]

Hi there, my first suspicion is that the names of your timelines are different, thus you need to modify every place in the code that refers to "NativeTime_Date_of_Appt" Go into your FORMULA/NAME MANAGER and look for all timeline names. For example, I have one called "NativeTimeline_FilterDate". Yours is probably different. Then fix the vba code to reference your timeline names. Hope this helps.

Hi Roderick,

Thanks for the advice. I've checked that the names of the timelines are correct. However, it still return me Run-time error 1004 (Application-defined or object-defined error).