How can i create and export a calculated field using wb_add_pivot_table from openxlsx2

Question

I tried to create a proportion among total setoff and total collectable using openxlsx2:add_pivot_table .

library(pivottabler)
library(openxlsx2)

wbs <- wb_workbook()$add_worksheet()$add_data(x = final_data, na.strings = NULL)
df <- wb_data(wbs)


# use TrainCategory on column and data
wbs$add_pivot_table(
  df, 
  rows ="REGION",
  #cols = " Collection efficiency",
  data =  "total_due","total_setoff","total_setoff/total_due",
  fun = "sum"
)
wbs$open()

but I am unable create with this code . I need to get the output like this.

i need to create and export a calculated field using wb_add_pivot_table from openxlsx2 in R

Answer 1

Calculating pivot table cells is not implemented (and atm not even on the to-do list), therefore if you want to calculate the percentages via a pivot table in openxlsx2 you have to work around this limitation.

You can add a formula in a cell next to the pivot table, assuming that you know the size of the table.

library(openxlsx2)

wb <- wb_workbook() %>% wb_add_worksheet() %>% wb_add_data(x = mtcars)

df <- wb_data(wb, sheet = 1)

wb$add_pivot_table(df, rows = "cyl", 
                   data = c("disp", "hp", "am"))

# either this
wb$add_formula(dims = "E5", x = 'GETPIVOTDATA("Sum of am",$A$3,"cyl",4)/GETPIVOTDATA("Sum of hp",$A$3,"cyl",4)')

# or that
wb$add_formula(dims = "F5", x = 'D5/C5')

if (interactive()) wb$open()

---- Edit ----

Two months later and I have just pushed a commit to the development branch that enables what OP was asking for.

data <- read.table(
  text=
"Row_Labels;total_setoff;total_collectable
Central;127944315.8;156853892.4
Eastern;64238224.91;95996589.61
North Central;88591752.32;118685530.1
North Western;70722084.95;88106333.94
Northern;83675702.59;109783241.2
Sabaragamuwa;106480991.8;131825649.1
Southern;100330799;133978792.5
Uva;56926208.9;75403444.66
Western - Colombo;149119137.1;177163558.3
Western - Gampaha;63703105.58;79348952.66
Western - Kalutara;32875233.8;45008877.09",
sep = ";", header = TRUE
)


library(openxlsx2)
wb <- wb_workbook()$add_worksheet()$add_data(x = data)

df <- wb_data(wb)

wb <- wb %>%
  wb_add_pivot_table(
    df, 
    dims = "A3",
    cols = NULL,
    rows = "Row_Labels",
    data = c("total_setoff", "total_collectable", "=total_setoff/total_collectable" = "prop"),
    param = list(
      numfmt = c(
        formatCode = c("#,##0.0"),
        formatCode = c("#,##0.0"),
        formatCode = c("0.0%")
      )
    )
  )

if (interactive()) wb$open()