Add a slicer/timeline to a previously created pivot table. This function is still experimental and might be changed/improved in upcoming releases.
Usage
wb_add_slicer(
wb,
x,
dims = "A1",
sheet = current_sheet(),
pivot_table,
slicer,
params
)
wb_remove_slicer(wb, sheet = current_sheet())
wb_add_timeline(
wb,
x,
dims = "A1",
sheet = current_sheet(),
pivot_table,
timeline,
params
)
wb_remove_timeline(wb, sheet = current_sheet())
Arguments
- wb
A Workbook object containing a worksheet.
- x
A
data.frame
that inherits thewb_data
class.- dims
The worksheet cell where the pivot table is placed
- sheet
A worksheet
- pivot_table
The name of a pivot table
- slicer, timeline
A variable used as slicer/timeline for the pivot table
- params
A list of parameters to modify pivot table creation. See Details for available options.
Details
This assumes that the slicer/timeline variable initialization has happened before. Unfortunately, it is unlikely that we can guarantee this for loaded workbooks, and we strictly discourage users from attempting this. If the variable has not been initialized properly, this may cause the spreadsheet software to crash. Although it is documented that slicers should use "TimelineStyleLight[1-6]" and "TimelineStyleDark[1-6]" they use slicer styles.
Possible params
arguments for slicers are listed below.
edit_as: "twoCell" to place the slicer into the cells
column_count: integer used as column count
sort_order: "descending" / "ascending"
choose: select variables in the form of a named logical vector like
c(agegp = 'x > "25-34"')
for theesoph
dataset.locked_position
start_item
Possible params
arguments for timelines are listed below.
beg_date/end_date: dates when the timeline should begin or end
choose_beg/choose_end: dates when the selection should begin or end
scroll_position
show_selection_label
show_time_level
show_horizontal_scrollbar
Possible common params
:
caption: string used for a caption
style: "SlicerStyleLight[1-6]", "SlicerStyleDark[1-6]" only for slicer "SlicerStyleOther[1-2]"
level: the granularity of the slicer (for timeline 0 = year, 1 = quarter, 2 = month)
show_caption: logical if caption should be shown or not
Removing works on the spreadsheet level. Therefore all slicers/timelines are removed from a worksheet. At the moment the drawing reference remains on the spreadsheet. Therefore spreadsheet software that does not handle slicers/timelines will still show the drawing.
See also
Other workbook wrappers:
base_font-wb
,
col_widths-wb
,
creators-wb
,
grouping-wb
,
row_heights-wb
,
wb_add_chartsheet()
,
wb_add_data()
,
wb_add_data_table()
,
wb_add_formula()
,
wb_add_hyperlink()
,
wb_add_pivot_table()
,
wb_add_worksheet()
,
wb_base_colors
,
wb_clone_worksheet()
,
wb_copy_cells()
,
wb_freeze_pane()
,
wb_merge_cells()
,
wb_save()
,
wb_set_last_modified_by()
,
wb_workbook()
Other worksheet content functions:
col_widths-wb
,
filter-wb
,
grouping-wb
,
named_region-wb
,
row_heights-wb
,
wb_add_conditional_formatting()
,
wb_add_data()
,
wb_add_data_table()
,
wb_add_formula()
,
wb_add_hyperlink()
,
wb_add_pivot_table()
,
wb_add_thread()
,
wb_freeze_pane()
,
wb_merge_cells()
Examples
# prepare data
df <- data.frame(
AirPassengers = c(AirPassengers),
time = seq(from = as.Date("1949-01-01"), to = as.Date("1960-12-01"), by = "month"),
letters = letters[1:4]
)
# create workbook
wb <- wb_workbook()$
add_worksheet("pivot")$
add_worksheet("data")$
add_data(x = df)
# get pivot table data source
df <- wb_data(wb, sheet = "data")
# create pivot table
wb$add_pivot_table(
df,
sheet = "pivot",
rows = "time",
cols = "letters",
data = "AirPassengers",
pivot_table = "airpassengers",
params = list(
compact = FALSE, outline = FALSE, compact_data = FALSE,
row_grand_totals = FALSE, col_grand_totals = FALSE)
)
# add slicer
wb$add_slicer(
df,
dims = "E1:I7",
sheet = "pivot",
slicer = "letters",
pivot_table = "airpassengers",
params = list(choose = c(letters = 'x %in% c("a", "b")'))
)
# add timeline
wb$add_timeline(
df,
dims = "E9:I14",
sheet = "pivot",
timeline = "time",
pivot_table = "airpassengers",
params = list(
beg_date = as.Date("1954-01-01"),
end_date = as.Date("1961-01-01"),
choose_beg = as.Date("1957-01-01"),
choose_end = as.Date("1958-01-01"),
level = 0,
style = "TimeSlicerStyleLight2"
)
)