R: Getting incorrect result in added summarised column

R: How to plot rpivotTable or dcast table with summarised column in between same as excel for reporting.

Check data set, tried different way to add summarised column in dcast and rpivottable but not getting it.

Check below code where i getting incorrect total value in summarised column.

Sample Data set be like.

Buyer       year_month(order)       

A           2016-01         
B           2016-01         
C           2016-02         
A           2016-04                 
A           2016-01     
A           2017-01         
B           2017-01         
C           2017-02         
A           2017-04 
A           2017-05         
B           2017-05         
C           2017-06         
A           2017-08     
B           2018-01         
C           2018-02         
A           2018-04         
A           2018-03         
B           2018-03         
C           2018-05         
A           2018-06         
B           2018-07         
A           2018-11         
B           2018-11         
A           2019-01         
B           2019-01         
A           2019-01         
A           2019-02         

Code i used but getting incorrect data.

library( data.table )
library( janitor )

#set data to data.table format
data.table::setDT(sub_data7)

sub_data7[, c("year", "month") := data.table::tstrsplit( year_month, "-" ) ][]

l <- lapply( unique(sub_data7$year),
             function(x) {
                          temp <- sub_data7[ year == x, ]
                          ans <- data.table::dcast(sub_data7, Buyer ~ year_month, fill = 0 )
                          ans <- janitor::adorn_totals(ans, where = c("col"), name = paste0( x, "_Total"))
} )

#bind together
ans <- data.table::rbindlist( l, use.names = TRUE, fill = TRUE )

#melt to ling
ans <- data.table::melt( ans, id.vars = "Buyer" )

ans <- ans[, sum(value, na.rm = TRUE), by = .(Buyer, variable)]

#get the order right
colorder = c("Buyer", sort( names(final)[!names(final) == "Buyer"] ) )


datatable(janitor::adorn_totals( final[, ..colorder ], where = "row" ))

But Getting incorrect value in total column.

you need to use below code.

library( data.table )
library( janitor )

#add new column as countdate, or elese we've got nothing to sum..
sub_data7 <-mutate(sub_data7,Countdate = ifelse(is.na(sub_data7$year_month),0,1))

#set data to data.table format
data.table::setDT(sub_data7)

#get yearly summarise, with totals
l <- lapply( unique(sub_data7$year),
             function(x) {
                          temp <- sub_data7[ year == x, ]
                          ans <- data.table::dcast(temp[, .(sum(Countdate)), by = .(Buyer, year,Month) ],Buyer ~ year+Month, fill=0)
                          ans <- janitor::adorn_totals(ans, where = c("col"), name = paste0( x, "_Total"))
                          } )

.
.
. # code same it is.
.

#reorder, and get totals by column
datatable(janitor::adorn_totals( final[, ..colorder ], where = "row" ),options = list(pageLength = 25))