I have the shiny app with nested tabs in the following order:
sidebarMenu(
menuItem("1000.Cash", tabName = "cash1000",
menuSubItem("CashInHand", tabName = "table1010"),
menuSubItem("CashInBankAccount", tabName = "table1020"))
where:
menuItem(“1000.Cash”, tabName = “cash1000”) contains the data.table “DF1000”;
menuSubItem(“CashInHand”, tabName = “table1010”) contains the data.table “DF1010”
menuSubItem(“CashInBankAccount”, tabName = “table1020”) contains the data.table “DF1020”
I am trying to construct the following algorithm :
“DF1000” is a higher level data.table wich summmarizes data from both “DF1010” and “DF1020”.
The summarization is to be done in a date range defined by a user with the dateRangeInput() widget.
The dateRangeInput() widget is located inside menuItem(“1000.Cash”, tabName = “cash1000”) as well.
As per conditional summarization shown in the reprex below, when we choose the date range, for instance, between 2020-01-01 and 2021-01-01 we should get the data.table “DF1000” as given below:
Accounts Beginning Debit Credit Ending
balance balance
-
CashInHand 250 70 50 270 -
CashInBankAccount 250 70 50 270 -
TOTAL: 500 140 100 540
However, the code is giving Error in [.data.frame: unused argument (by = DocName).
Can someone help me figure out the way to handle this error?
library(shiny)
library(shinydashboard)
library(rhandsontable)
library(data.table)
library(dplyr)
DF1000 <- data.table(
"Accounts" = as.character(c("CashInHand",
"CashInBankAccount",
"TOTAL:")),
"Beginning balance" = as.numeric(c(0,0,0)),
Debit = as.numeric(c(0,0,0)),
Credit = as.numeric(c(0,0,0)),
"Ending balance" = as.numeric(c(0,0,0)),
stringsAsFactors = FALSE)
DF1010 <- data.table(
"TransactionDate" = as.character(c("01/01/2020", "01/01/2020", "01/01/2021", "01/01/2021")),
"DocName" = as.character(c("ab", "de","ab", "de")),
"Beginning balance" = as.numeric(c(100, 150, 105, 155)),
"Debit" = as.numeric(c(20, 10, 25, 15)),
"Credit" = as.numeric(c(15, 5, 20, 10)),
"Ending balance" = as.numeric(c(105, 155, 110, 160)),
stringsAsFactors = FALSE)
DF1010 <- data.table(
"TransactionDate" = as.character(c("01/01/2020", "01/01/2020", "01/01/2021", "01/01/2021")),
"DocName" = as.character(c("lm", "xy","lm", "xy")),
"Beginning balance" = as.numeric(c(100, 150, 105, 155)),
"Debit" = as.numeric(c(20, 10, 25, 15)),
"Credit" = as.numeric(c(15, 5, 20, 10)),
"Ending balance" = as.numeric(c(105, 155, 110, 160)),
stringsAsFactors = FALSE)
ui <- fluidPage(
dashboardPage(
dashboardHeader(title = "IFRS"),
dashboardSidebar(
sidebarMenu(
menuItem("1000.Cash", tabName = "cash1000",
menuSubItem("CashInHand", tabName = "table1010"),
menuSubItem("CashInBankAccount", tabName = "table1020")
)
)
),
dashboardBody(
tabItems(
tabItem(tabName = "cash1000",
fluidRow(
column(
width = 6,
label=NULL,
dateRangeInput("dates1000", "Choose a period:", format = "dd/mm/yyyy",
start = "2020-01-01", end = Sys.Date()),
uiOutput("nested_ui1000")),
column(
width = 8,
"Summary journal: 1000.Cash ",
rHandsontableOutput("table1000Item1")
)
)
)
),
tabItem(tabName = "table1010",
fluidRow(
column(
width = 8,
"Journal of entries",
rHandsontableOutput("table1010Item1")
)
)
),
tabItem(tabName = "table1020",
fluidRow(
column(
width = 8,
"Journal of entries",
rHandsontableOutput("table1020Item1")
)
)
)
)
)
)
server = function(input, output) {
data <- reactiveValues()
observe({
data$df1000 <- as.data.frame(DF1000)
data$df1010 <- as.data.frame(DF1010)
data$df1020 <- as.data.frame(DF1020)
})
observe({
if(!is.null(input$table1000Item1))
data$df1000<- hot_to_r(input$table1000Item1)
})
observe({
if(!is.null(input$table1010Item1))
data$df1010<- hot_to_r(input$table1010Item1)
})
observe({
if(!is.null(input$table1020Item1))
data$df1020<- hot_to_r(input$table1020Item1)
})
observe({
data$df1000[1, c(2:5)] <- data$df1010[, .(
"Beginning balance" = sum("Beginning balance", na.rm = TRUE),
Debit = sum(Debit, na.rm = TRUE),
Credit = sum(Credit, na.rm = TRUE),
"Ending balance" = sum("Ending balance", na.rm = TRUE)
), by = DocName][, .(
"Beginning balance" = sum("Beginning balance"),
Debit = sum(Debit),
Credit = sum(Credit),
"Ending balance" = sum("Ending balance")
)][, c(3:6)]
})
observe({
data$df1000[2, c(2:5)] <- data$df1020[, .(
"Beginning balance" = sum("Beginning balance", na.rm = TRUE),
Debit = sum(Debit, na.rm = TRUE),
Credit = sum(Credit, na.rm = TRUE),
"Ending balance" = sum("Ending balance", na.rm = TRUE)
), by = DocName][, .(
"Beginning balance" = sum("Beginning balance"),
Debit = sum(Debit),
Credit = sum(Credit),
"Ending balance" = sum("Ending balance")
)][, c(3:6)]
})
output$nested_ui1000 <- renderUI({
if (input$dates1000) {
dateRangeInput("dates", "Choose a period:", format = "dd/mm/yyyy",
start = "2020-01-01", end = Sys.Date())
}
})
output$table1000Item1 <- renderRHandsontable({
rhandsontable(data$df1000[c(1:2),c(2:5)], stretchH = "all", rowHeaderWidth = 50, height = 200)
})
output$table1010Item1 <- renderRHandsontable({
rhandsontable(data$df1010, stretchH = "all", rowHeaderWidth = 50, height = 200) |>
hot_col(1, format = "%d/%m/%Y", type = "date")
})
output$table1020Item1 <- renderRHandsontable({
rhandsontable(data$df1020, stretchH = "all", rowHeaderWidth = 50, height = 200) |>
hot_col(1, format = "%d/%m/%Y", type = "date")
})
}
shinyApp(ui = ui, server = server)