How to make a dynamic footer that references values in other dynamic footers?

[gilmeriken]

I have data that is NOT grouped: for example, revenue, units, and revenue per unit (the last already calculated). This seems to rule out the use of the AGGREGATE syntax.

The dynamic (JS) footers for revenue and units are easy to make using examples provided, as they are simply sums of the rows above.

However, the footer for revenue per unit is giving me some trouble. What is the syntax to call the other footers and divide the footer value for revenue for by the footer value for units to get a footer value for revenue per unit?

Alternatively, within the footer for revenue per unit, how do I sum the other columns and then divide by those sums?

Love the package, especially with crosstalk, it makes reactable super-powered with its select filters and checkbox filters!

To summarize: where dataframe consists of Revenue, Units, and RevenuePerUnit,

reactable( data, searchable = TRUE, defaultPageSize = 5, minRows = 5, columns = list( Revenue = colDef( footer = JS("function(colInfo) { var total = 0 colInfo.data.forEach(function(row) { total += row[colInfo.column.id] }) return '$' + total.toFixed(2) }"), Units = colDef( footer = JS("function(colInfo) { var total = 0 colInfo.data.forEach(function(row) { total += row[colInfo.column.id] }) return total.toFixed(2) }"), RevenuePerUnit = colDef ( footer = JS( _____ ) ), defaultColDef = colDef(footerStyle = list(fontWeight = "bold")) )

[glin]

Hi, you might find this similar example with aggregating values from different columns useful: https://glin.github.io/reactable/articles/examples.html#custom-aggregate-function

In short, you can get the cell values from other columns in a footer render function using the row objects in the colInfo.data array. In the example above, row[colInfo.column.id] gets the cell values for the current column, but you could also use row['Units'] to get values from the Units column, or row['Revenue'] to get values from the Revenue column.

There's no way to get footer values from other columns, so you'll have to sum up the Units and Revenue columns again in the RevenuePerUnit column footer.

Here's an example of this based on the aggregate function example I linked above. In my example, I use Price, Units, and Avg.Price as the column names, where the Avg.Price footer is calculated as sum(Price) / sum(Units).

library(reactable)
library(dplyr)

set.seed(10)

data <- sample_n(MASS::Cars93[23:40, ], 30, replace = TRUE) %>%
  mutate(Price = Price * 3, Units = sample(1:5, 30, replace = TRUE)) %>%
  mutate(Avg.Price = Price / Units) %>%
  select(Model, Manufacturer, Price, Units, Avg.Price)

reactable(
  data,
  searchable = TRUE,
  columns = list(
    Model = colDef(footer = "Total"),
    Price = colDef(
      format = colFormat(currency = "USD"),
      # Calculate the total Price as `sum(Price)`
      footer = JS("function(colInfo) {
        var total = 0
        colInfo.data.forEach(function(row) {
          total += row['Price']
        })
        return '$' + total.toFixed(2)
      }")
    ),
    Units = colDef(
      # Calculate the total Units as `sum(Units)`
      footer = JS("function(colInfo) {
        var total = 0
        colInfo.data.forEach(function(row) {
          total += row['Units']
        })
        return total
      }")
    ),
    Avg.Price = colDef(
      # Calculate the total Avg.Price as `sum(Price) / sum(Units)`
      footer = JS("function(colInfo) {
        var totalPrice = 0
        var totalUnits = 0
        colInfo.data.forEach(function(row) {
          totalPrice += row['Price']
          totalUnits += row['Units']
        })
        return '$' + (totalPrice / totalUnits).toFixed(2)
      }"),
      format = colFormat(currency = "USD")
    )
  ),
  defaultColDef = colDef(footerStyle = list(fontWeight = "bold"))
)

I'll probably add this to the docs.

[gilmeriken]

Thank you, Greg!