Order values inside a pivot table in openxlsx2

Question

I´m using openxlsx2 so that I can use the function wb_add_pivot_table() but the problem is that the order in which the rows appear, inside the pivot table, isn't in the order I want it to be displayed.

I´ve tried the following:

tbl_1 <- tibble(
  var_1 = c("FEBRERO", "SEPTIEMBRE","AGOSTO", "SEPTIEMBRE", "AGOSTO", "FEBRERO", "DICIEMBRE", "DICIEMBRE"),
  var_2 = seq_along(var_1)
) |> 
  mutate(
    var_1 = as.factor(var_1) |> 
      fct_relevel("FEBRERO", "AGOSTO", "SEPTIEMBRE", "DICIEMBRE")
  ) # I want the order of te rows, inside the pivot table, be like this
tbl_1
wb_1 <- wb_workbook() |> 
  wb_add_worksheet() |> 
  wb_add_data(x = tbl_1) 
df <- wb_data(wb_1)
wb_1 |> 
  wb_add_pivot_table(
    x = df,
    rows = "var_1",
    data = "var_2",
    fun = "sum"
  ) |> 
  wb_save("example_1.xlsx")

My naive approach, as you see, was to convert the class of the var_1 column to factor, but it didn't work. Thanks in advance.

Answer 1

I set up this question badly. The problem I really wanted to solve was this:

library(tidyverse); library(openxlsx2)

tbl_1 <- tibble(
  var_1 = seq(from = ymd("2023-01-01"),
              to = ymd("2024-03-31"),
              by = "month"),
  var_2 = seq_along(var_1)
) |> 
  uncount(2) |> 
  mutate(
    year = year(var_1),
    month = month(var_1, label = TRUE)
  )

With this I wanted the year and month columns be in the column field inside the pivot table. The problem with this is that the month column is not in the proper order, not Ene-Dic:

wb_1 <- wb_workbook() |>
  wb_add_worksheet() |>
  wb_add_data(x = tbl_1)
df <- wb_data(wb_1)
wb_1 |>
  wb_add_pivot_table(
    x = df,
    cols = c("year", "month"),
    data = "var_2",
    fun = "sum"
  )

This results in:

Now, according to the docs, the following should work:

wb_1 <- wb_workbook() |> 
  wb_add_worksheet() |> 
  wb_add_data(x = tbl_1) 
df <- wb_data(wb_1)
wb_1 |> 
  wb_add_pivot_table(
    x = df,
    cols = c("year", "month"),
    data = "var_2",
    fun = "sum",
    params = list(
      sort_item = list(month = 12:1)
    )
  )

And it does: