Set the visibility and captions of calculations depending on data groups

[MarcoPortmann]

I use pivottabler to display results of simulations. I have a reference scenario and would like to display result values (e.g. for production quantities and prices). I want to leave it up to the user whether the actual values ("Value" in the example code below) or the absolute ("AbsDiff") and relative ("RelDiff") differences to the reference scenario are displayed as well. For the reference scenario itself it makes no sense to display the differences (to itself).

I use a user-defined function three times to calculate Value, AbsDiff, RelDiff.

• So far I could not find a flexible option how to switch off a calculation depending on the scenario category (i.e. depending on a columndatagroup).
• Furthermore I would like to write the units into the caption, e.g. "Value [t]", "Value [€/kg]", "Abs. diff. [t]".

I have experimented with some alternatives. For example, the scenarios could be added via addColumnGroup and then different calculations for the reference scenario and the other scenarios could be defined via addColumnCalculationGroups. If I understand it correctly, this implies that the scenarios are always set as top-level and it would not be possible to flexibly change the order of the groups (indicators before scenarios, scenario groups before scenarios or similar).

Also for the display of the units I can't find a satisfying alternative solution instead of adapting the calculation caption. For example, if I introduce another data group for the units, I can display "€/kg" for the indicator value "Price" and "t" for "Production" (or I could display the unit in indicator captions (e.g. "Price [€/kg]") instead of adding an additional column data group). But then "€/kg" is the heading for all calculations (Value, AbsDiff, RelDiff), but the calculation "RelDiff" has the unit "%".

This is the reproducible example without the modified headers and with the RelDiff and Absdiff columns to be removed. The actual aggregation function is not included in the example (11 is the placeholder value).

library(pivottabler)
# User input: What should be displayed in the table (Value = original value,
# AbsDiff = absolute difference to the value from the reference scenario,
# RelDiff = relative difference to the value from the reference scenario)
ShowValType <- c("Value", "AbsDiff", "RelDiff")

# User input: The differences to the reference scenario need not be displayed
# for the reference scenario itself. It should also be possible to hide the
# original value for the reference scenario.
ShowRefVal <- T


TestData <- structure(list(Scen = c("Inc10", "Inc10", "Inc10", "Inc10", "Ref", "Ref", "Ref", "Ref"), 
                           Product = c("Beef", "Beef", "Poultry", "Poultry", "Beef", "Beef", "Poultry", "Poultry"), 
                           Indicator = c("Price", "Production", "Price", "Production", "Price", "Production", "Price", "Production"), 
                           Unit = c("€/kg", "t", "€/kg", "t", "€/kg", "t", "€/kg", "t"), 
                           Value = c(21, 260, 19.8, 165, 25, 200, 18, 150), 
                           IsReference = c(FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE), 
                           Fun = c("weighted.mean", "sum", "weighted.mean", "sum", "weighted.mean", "sum", "weighted.mean", "sum"), 
                           AggregationWeight = c("Production", "Production", "Production", "Production", "Production", "Production", "Production", "Production"), 
                           Unit.Aggr = c("t", "t", "t", "t", "t", "t", "t", "t"), 
                           Value.Aggr = c(260, 260, 165, 165, 200, 200, 150, 150), 
                           Scen.Ref = c("Ref", "Ref", "Ref", "Ref", "Ref", "Ref", "Ref", "Ref"), 
                           Value.Ref = c(25, 200, 18, 150, 25, 200, 18, 150),
                           AggregationWeight.Ref = c("Production", "Production", "Production", "Production", "Production", "Production", "Production", "Production"),
                           Unit.Aggr.Ref = c("t", "t", "t", "t", "t", "t", "t", "t"),
                           Value.Aggr.Ref = c(200, 200, 150, 150, 200, 200, 150, 150),
                           ShowRefVal = c(TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE)),
                      row.names = c(NA, -8L), class = c("tbl_df", "tbl", "data.frame"))  

TestData$Scen <- factor(TestData$Scen, c("Ref", "Inc10"), c("Reference", "Increase of 10 %"))
#

pt <- PivotTable$new()
pt$addData(TestData)
# Rows

TestData$ShowRefVal <- ShowRefVal

#
pt$addColumnDataGroups("Scen", addTotal = F)
pt$addColumnDataGroups("Indicator", addTotal = F)
pt$addRowDataGroups("Product")


# Add calculations

CalcFun <- function(pivotCalculator, netFilters, format, baseValues, cell){
  Tab <- pivotCalculator$getDataFrame("TestData")
  TabFilt <- pivotCalculator$getFilteredDataFrame(Tab, netFilters)
  
  # Placeholder: Code that applies an indicator-specific aggregation function.
  # See TestData columns Fun, AggregationWeight.
  
  # Placeholder: cell$calculationName allows me to decide whether to calculate
  # the original value, the absolute or the relative difference to the reference
  # scenario. (I could of course also use three specific functions CalcValue,
  # CalcAbs, CalcRel. However, most of the CalcFun would be identical for all
  # three).
  
  # <<< Here I would have the possibility to decide based on TabFilt$ShowRefVal
  # and cell$calculationName whether the corresponding calculation should be
  # displayed or not. >>>
  
  value <- list()
  value$rawValue <- 11
  value$formattedValue <- pivotCalculator$formatValue(value$rawValue)
  return(value)
}

if ("Value" %in% ShowValType)
  pt$defineCalculation(calculationName="Value", caption = "Value",
                       type = "function",
                       calculationFunction = CalcFun)
if ("AbsDiff" %in% ShowValType)
  pt$defineCalculation(calculationName="AbsDiff", caption = "Abs. diff.",
                       type = "function",
                       calculationFunction = CalcFun)
if ("RelDiff" %in% ShowValType)
  pt$defineCalculation(calculationName="RelDiff", caption = "Rel. diff.",
                       type = "function",
                       calculationFunction = CalcFun)
pt$evaluatePivot()
pt$renderPivot()

Schematic representation of the desired changes:

I'm not sure if I should call the issue a feature request, or if I haven't read the documentation carefully enough. I would be grateful for a response.

[cbailiss]

Hi Marco
Some interesting questions here.

My first impression is that you are attempting to put a lot of logic into the custom calculation function. A custom calculation function is normally only used to calculate cells values rather than changing the structure of the pivot table.
I.e. the general approach to building a pivot table is:

1. build the structure (row groups, columns groups, set the calculation definitions),
2. generate the cells, and
3. finally calculate the cell values.

Custom calculation functions are only executed in step 3 - where as the structure is normally set/fixed before this.

However, your general requirement makes sense (if I am understanding it all correctly!).

You have highlighted an interesting feature requirement that the package doesn't currently support - the need to pass additional arguments to custom calculation functions. The example below works around this by creating three very thin wrapper functions around your bigger custom calculation function. I will add a new feature request for the future around this (so in the future you will be able to pass additional arguments to the custom function directly without needing these thin wrappers).

You have also highlighted a need to be able to more selectively add calculations to a pivot table (e.g. to selectively add only the "Value" calculation under "Reference". I will add a new feature request for this too - though will need to give it some thought about how best to implement this.

For now however, it is possible to work around the above two things...

So the example below (based on your code) removes columns not needed. The example shows two different ways of doing this - a simple (but hard-coded) way and a more dynamic way that doesn't need to hard-code column numbers.
The example below also sets column captions outside the custom calculation function.
All of this is done before the cells are generated and their values calculated.

There is also one way you could set the captions from within the custom calculation function but I don't really like doing this for the reasons described above (an example of this is included but commented out below) and the code is also quite ugly.

The example doesn't set all of the column captions because I couldn't see from the picture what every column caption should be, but hopefully you can use build on these examples for the other columns.

library(pivottabler)
# User input: What should be displayed in the table (Value = original value,
# AbsDiff = absolute difference to the value from the reference scenario,
# RelDiff = relative difference to the value from the reference scenario)
ShowValType <- c("Value", "AbsDiff", "RelDiff")

# User input: The differences to the reference scenario need not be displayed
# for the reference scenario itself. It should also be possible to hide the
# original value for the reference scenario.
ShowRefVal <- T

TestData <- structure(list(Scen = c("Inc10", "Inc10", "Inc10", "Inc10", "Ref", "Ref", "Ref", "Ref"), 
                           Product = c("Beef", "Beef", "Poultry", "Poultry", "Beef", "Beef", "Poultry", "Poultry"), 
                           Indicator = c("Price", "Production", "Price", "Production", "Price", "Production", "Price", "Production"), 
                           Unit = c("€/kg", "t", "€/kg", "t", "€/kg", "t", "€/kg", "t"), 
                           Value = c(21, 260, 19.8, 165, 25, 200, 18, 150), 
                           IsReference = c(FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE), 
                           Fun = c("weighted.mean", "sum", "weighted.mean", "sum", "weighted.mean", "sum", "weighted.mean", "sum"), 
                           AggregationWeight = c("Production", "Production", "Production", "Production", "Production", "Production", "Production", "Production"), 
                           Unit.Aggr = c("t", "t", "t", "t", "t", "t", "t", "t"), 
                           Value.Aggr = c(260, 260, 165, 165, 200, 200, 150, 150), 
                           Scen.Ref = c("Ref", "Ref", "Ref", "Ref", "Ref", "Ref", "Ref", "Ref"), 
                           Value.Ref = c(25, 200, 18, 150, 25, 200, 18, 150),
                           AggregationWeight.Ref = c("Production", "Production", "Production", "Production", "Production", "Production", "Production", "Production"),
                           Unit.Aggr.Ref = c("t", "t", "t", "t", "t", "t", "t", "t"),
                           Value.Aggr.Ref = c(200, 200, 150, 150, 200, 200, 150, 150),
                           ShowRefVal = c(TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE)),
                      row.names = c(NA, -8L), class = c("tbl_df", "tbl", "data.frame"))  

TestData$Scen <- factor(TestData$Scen, c("Ref", "Inc10"), c("Reference", "Increase of 10 %"))
#

pt <- PivotTable$new()
pt$addData(TestData)
# Rows

TestData$ShowRefVal <- ShowRefVal

#
pt$addColumnDataGroups("Scen", addTotal = F)
pt$addColumnDataGroups("Indicator", addTotal = F)
pt$addRowDataGroups("Product")


# Add calculations

CalcFunBase <- function(ValType, pivotCalculator, netFilters, format, baseValues, cell){
  Tab <- pivotCalculator$getDataFrame("TestData")
  TabFilt <- pivotCalculator$getFilteredDataFrame(Tab, netFilters)
  
  # calculate value
  if(ValType=="Value") {
    # calculate
  }
  else if(ValType=="AbsDiff") {
    # calculate
  }
  else if(ValType=="RelDiff") {
    # calculate
  }
  
  # here is one way of setting column headings from inside the custom 
  # function (but I don't really like this approach since a custom
  # cell calculation function should not alter the structure
  # of the pivot table).  It feels a bit messy. If you really wanted to do it....
  # (only need to set the captions once, so let's just do this 
  # when we are calculating the cell values in in row 1...)
  # if(cell$rowNumber==1) {
  #   columnFilters <- cell$columnFilters
  #   if(columnFilters$getFilter("Scen")$values=="Reference") {
  #     if(columnFilters$getFilter("Indicator")$values=="Price") {
  #       if(cell$calculationName=="Value") {
  #         cell$columnLeafGroup$caption <- "Value [€/kg]"
  #       }
  #       # etc (i.e. need lots more if blocks or similar...)
  #     }
  #   }
  # }
  
  value <- list()
  value$rawValue <- cell$rowNumber
  value$formattedValue <- pivotCalculator$formatValue(value$rawValue)
  return(value)
}

CalcFunValue <- function(pivotCalculator, netFilters, format, baseValues, cell) {
  return(CalcFunBase("Value", pivotCalculator, netFilters, format, baseValues, cell))
}

CalcFunAbsDiff <- function(pivotCalculator, netFilters, format, baseValues, cell) {
  return(CalcFunBase("AbsDiff", pivotCalculator, netFilters, format, baseValues, cell))
}

CalcFunRelDiff <- function(pivotCalculator, netFilters, format, baseValues, cell) {
  return(CalcFunBase("RelDiff", pivotCalculator, netFilters, format, baseValues, cell))
}

if ("Value" %in% ShowValType)
  pt$defineCalculation(calculationName="Value", caption = "Value",
                       type = "function",
                       calculationFunction = CalcFunValue)
if ("AbsDiff" %in% ShowValType)
  pt$defineCalculation(calculationName="AbsDiff", caption = "Abs. diff.",
                       type = "function",
                       calculationFunction = CalcFunAbsDiff)
if ("RelDiff" %in% ShowValType)
  pt$defineCalculation(calculationName="RelDiff", caption = "Rel. diff.",
                       type = "function",
                       calculationFunction = CalcFunRelDiff)

# simple way of setting captions and removing groups:
# addColumnCalculationGroups() adds the calculation columns to the pivot table
# and returns a list of the new column groups that have been added. This list
# can be used to modify the captions, remove the groups, etc
grps <- pt$addColumnCalculationGroups()
grps[[1]]$caption <- "Value [€/kg]"
grps[[2]]$removeGroup()
grps[[3]]$removeGroup()
grps[[4]]$caption <- "Value [t]"
grps[[5]]$removeGroup()
grps[[6]]$removeGroup()
grps[[12]]$caption <- "Rel Diff. [%]"

# more dynamic way (changing the colour as an example)
grp <- pt$findColumnDataGroups(matchMode="combinations",
                               variableValues=list("Scen"="Increase of 10 %", "Indicator"="Price"),
                               calculationNames="Value")
pt$setStyling(groups=grp, declarations=list("background-color"="#00FFFF"))
# this example just changes the colour, but could change the caption or remove the group as shown below
# grp$caption <- "Value [€/kg]"
# grp$removeGroup()

# at this point, only the row and column headings exist (no cell values have been calculated yet)

pt$evaluatePivot()
pt$renderPivot()

Output:

[cbailiss]

Issues #32 and #33 created in response to the above discussion. Aim for release in early May. For now, workarounds are as discussed in the longer response above.

[MarcoPortmann]

Hello, Chris,

thanks a lot for your great support! I have now managed to adapt the table with the dynamic variant (findColumnDataGroups) as I wanted.

I had actually stuck more to the "general approach to building a pivot" during a first attempt. It would be possible and easy to create AbsDiff and RelDiff before the pivot table and define columnDataGroup valueTypes = Value, RelDiff, AbsDiff. But it gets more complicated with the aggregates. These must be generated in step 3. I also came across the related issue #2. At the same time I have to say: if the aggregation should be as generic as possible (different data, dynamic filters set by the user), then the aggregation is always complex, whether it is carried out in advance or not.

The reason why I initially intended to set the visibility of columns and rows in the custom calculation function: I first assumed that only all cells in a column or row would have to be NULL and then it would be deleted auotmatically. This would be analogous to the onlyCombinationsThatExist=FALSE option.

[cbailiss]

Thanks for the reply.
I'll give some more thought to #2.
The ability to easily remove entirely null rows/columns is a good idea. #34 added.