Pivot Table Timeline Slicer

Timeline slicers are interactive filters that allow you to filter data quickly by date, month, year, and quarter. A visual interface is provided with controllers to help you zoom in and out on any period to view and filter the pivot data.

The timeline slicer only works on date fields of a pivot table using a label condition filter. It can be added to a pivot table using the Slicers.add class method. Set PivotTimeline as the slicer type and apply TimelineLevel enumeration options such as years, quarters, months, and dates to specify how to filter the data in a slicer.

The PivotTableTimelineSlicer class provides various methods to customize the slicer UI.

Use Case Scenario

Consider the example where the food sales company selling food and beverage products wants to display the sales data of products across various quarters of a year.

The timeline slicer can help in analyzing the data based on the period as illustrated in the GIF below.

// Add timeline slicers for year, quarter, month filters
var timeline_year = sheet.slicers.add("timeline_year", pt.name(), "OrderDate", GC.Spread.Sheets.Slicers.TimelineStyles.dark6(), GC.Spread.Sheets.Slicers.SlicerType.pivotTimeline);
timeline_year.position(new GC.Spread.Sheets.Point(600, 10));
timeline_year.level(GC.Spread.Sheets.Slicers.TimelineLevel.years);
timeline_year.showSelectionLabel(false);
timeline_year.showTimeLevel(false);
timeline_year.showHorizontalScrollbar(false);
timeline_year.height(100);
timeline_year.captionName("Years");

var timeline_quarter = sheet.slicers.add("timeline_quarter", pt.name(), "OrderDate", GC.Spread.Sheets.Slicers.TimelineStyles.dark1(), GC.Spread.Sheets.Slicers.SlicerType.pivotTimeline);
timeline_quarter.position(new GC.Spread.Sheets.Point(600, 130));
timeline_quarter.level(GC.Spread.Sheets.Slicers.TimelineLevel.quarters);
timeline_quarter.captionName("Quarters");
timeline_quarter.showSelectionLabel(false);

var timeline_month = sheet.slicers.add("timeline_month", pt.name(), "OrderDate", GC.Spread.Sheets.Slicers.TimelineStyles.light4(), GC.Spread.Sheets.Slicers.SlicerType.pivotTimeline);
timeline_month.position(new GC.Spread.Sheets.Point(600, 290));
timeline_month.captionName("Months");
timeline_month.showTimeLevel(false);

Select Slicer Items Using Designer

In the SpreadJS Designer, the following actions can be performed to select pivot table slicer items:

• Actions: Click/Drag

• Modes: Single/Multi

• Additional keys: Ctrl/Shift/None

Set Custom Timeline Slicer Styles

SpreadJS provides 6 light and 6 dark predefined themes which can be applied to a pivot table timeline slicer. However, you can manage custom pivot table timeline slicer themes by adding, removing, and modifying slicer styles, which can then be applied to your pivot table slicers. To use these styles, you need to just set a style name for the timeline slicer. To set timeline slicer styles, use the customTimelineThemes property of the GC.Spread.Sheets.WorkBook class.

The CustomTimelineThemesManager class provides the following methods to manage custom timeline slicer themes:

Additionally, the defaultTimelineTheme method of the GC.Spread.Sheets.WorkBook class enables you to manage default timeline slicer styles effectively to ensure a consistent look and feel across all timeline slicers.

Notes:

• Timeline slicer theme names must be unique.

  For example, if you already have a custom timeline slicer style named "TEST", you cannot create another timeline slicer style with the same name.

• Timeline slicer style names are case-insensitive.

• When a style name is added to a timeline slicer, it finds the style by the name. If the style name matches an internal style, it will be applied directly. If not, it will search for the corresponding style in the customStyles collection. If found, that style will be applied. Otherwise, the default style will be used.

• The defaultTimelineTheme should be a string representing the style name of the timeline slicer. If a slicer is created without explicitly specifying a style, and if, a style with this default name exists, then the style will be applied to that slicer.

• Custom timeline slicer themes do not support older versions of Excel IO.

The following code samples demonstrate different methods necessary to manage custom timeline slicer themes.

1. Add a new custom timeline slicer theme.

   const spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
   
   // Add a new timeline slicer theme named “custom0”.
   const slicerTheme = spread.customTimelineThemes.add("custom0");
   const styleInfo = new GC.Spread.Sheets.Slicers.SlicerStyleInfo();
   styleInfo.backColor("orange");
   styleInfo.borderBottom(new GC.Spread.Sheets.Slicers.SlicerBorder(2,"solid","green"));
   slicerStyle.wholeSlicerStyle(styleInfo);

2. Get the custom timeline slicer theme.

   spread.customTimelineThemes.get("custom0");

3. Apply the custom timeline slicer theme.

   timeline.style("custom0");

4. Modify the custom timeline slicer theme.

   When you make changes to an existing timeline slicer style, it will automatically update all slicers where that style was previously applied.

   // Modify timeline slicer theme.
   const slicerStyle = spread.customTimelineThemes.get("custom0");
   slicerStyle.wholeSlicerStyle().backColor("red");
   
   // Update timeline slicer theme.
   spread.customTimelineThemes.update("custom0", new GC.Spread.Sheets.Slicers.TimelineStyle());

5. Delete an existing custom timeline slicer theme.

   When you delete a custom timeline slicer style, all slicers that used the deleted style will switch to the default style.

   // Delete timeline slicer theme.
   spread.customTimelineThemes.remove("custom0");

6. Set default timeline slicer theme.

   // Set Default timeline slicer theme.
   spread.defaultTimelineTheme("custom0");

You can also add and format timeline slicer styles using the SpreadJS Designer Component by following the steps below:

1. Click on the added timeline slicer to open the TIMELINE tab to configure the timeline settings.

2. Click the Styles button available in the Timeline Styles group.

   
   

   
   

   
   

3. Select the New Slicer Style.. option, which opens the New Slicer Style dialog.

   
   

   
   

   
   

4. Enter a new style name in the Name text field, select the element from the Slicer Element section to which you want to apply the styles, and then click Format to configure your desired styles for the custom timeline slicer theme.

   
   

   
   

   
   

5. All the user-created timeline slicer styles are displayed under the Custom section of the slicer styles collection list.

   
   

   
   

   
   

6. Right-click on the custom timeline slicer style and perform actions such as apply, modify, duplicate, delete, and set as default actions, as per your requirement.

   
   

   
   

   
   

Note: You cannot modify or delete built-in pivot table timeline slicer styles.