R/aging.R

In Chiarini/icrmkpi: KPI formula

#---------------------------------------------------------------------------------#
#' aging File loading --------------------------------------------------------------
#---------------------------------------------------------------------------------#
#' @param file file
#' @return tibble object
#' @export
#' @examples
#' company_data <- load_company_region ("~/data/icrmkpi/iwo/", 'company-region.csv')
#' debugonce(load_aging)
#' aging_data <- load_aging('~/data/icrmkpi/aging/aging-G048-201909.csv', company_data = company_data)
#' aging_data <- load_aging('~/data/icrmkpi/aging/aging-G046-202002.csv', company_data = company_data)
#'
load_aging <- function(file, company_data){

  company_data <- company_data %>%
    mutate(company_input = paste(company, company_name , sep = '-')) %>%
    select (company, company_name, company_input)


  # collect last day of month from fiel name :-(
  #the_date <- ymd(paste(str_split(str_split(file, '-')[[1]][[3]], '\\.')[[1]][[1]], '01', sep = '')) + days_in_month(the_date) - 1
  the_date <- ymd(paste(
    str_split(str_split(file, '-')[[1]][[3]], '\\.')[[1]][[1]]
    , '01', sep = ''))

  the_date <- the_date  + days_in_month(the_date) - 1



  col_name <- c('company','sap_document_no' , 'account_year' , 'account_number', 'asset' , 'da', 'description' , 'type', 'sector' ,'posting_date', 'amount')
  col_type <- 'ccicccccccc'

  data <- read_delim(file,delim = ";",skip = 1,col_names = col_name,col_types = col_type)

  data <- data %>%
    left_join(company_data , by = 'company')

  # fix amount
  data <- data %>%
    mutate(amount=str_replace_all(amount,"\\.",""),
           amount=str_replace_all(amount,",","."),
           amount=as.numeric(amount))

  # data <- data %>%
  #   mutate(company=str_sub(company,4),
  #          company_input = paste(company, company_name, sep = '-'),
  #          g_l_account=str_sub(g_l_account,4),
  #          mastro = str_sub(g_l_account,1,4))

  # fix dates
  data <- data %>%
    mutate(posting_date=dmy(posting_date),
           posting_year = year(posting_date),
           posting_month = month(posting_date),
           date = the_date,
           year = year(the_date),
           month = month(the_date))

  # data <- data %>%
  #   mutate(asset = ifelse(str_detect(asset, '*'), '0', asset))

  data <- data %>%
    select (company,  company_name, company_input, account_number, asset, posting_date,   amount , posting_year,  posting_month, date, year , month )

  data

}

#---------------------------------------------------------------------------------#
#' imports all aging files --------------------------------------------------------
#---------------------------------------------------------------------------------#
#' @export
#' @examples
#' company_data <- load_company_region ("~/data/icrmkpi/iwo/", 'company-region.csv')
#' all_aging_data <- load_aging_all("~/data/icrmkpi/aging/", company_data = company_data)
load_aging_all <- function(data_dir, company_data) {
  pattern <-  'aging'
  file_list <- list.files(data_dir,full = TRUE, pattern = pattern)
  all_data <- lapply(file_list, load_aging, company_data = company_data) %>% bind_rows()
  all_data
}

#---------------------------------------------------------------------------------#
#' imports list of account --------------------------------------------------------
#---------------------------------------------------------------------------------#
# @export
# @examples
# account_data <- load_account("~/data/icrmkpi/aging/", 'list-account.csv')
# load_account <- function(data_dir, file) {
#
#   account_data <- read_delim(file.path(data_dir, file) , delim = ';', col_type = 'cccc')
#   account_data
#
# }

#---------------------------------------------------------------------------------#
#' Prepare aging data  ------------------------------------------------------------
#---------------------------------------------------------------------------------#
#' @export
#' @examples
#'  company_data <- load_company_region ("~/data/icrmkpi/iwo/", 'company-region.csv')
#'  data <- load_aging_all("~/data/icrmkpi/aging/", company_data = company_data)
#' asset_data <- prepare_aging(data,.company = 'G048', .year = 2020, .month = 3, rolling = 13)
prepare_aging <- function(data, .company, .year, .month ,rolling = 13) {

  last_date <- ymd(paste(.year, .month,'1'))
  last_date <- last_date +days_in_month(last_date) -1


  open_asset_data  <- data %>%
    group_by(company, company_name, company_input , account_number, asset, date ) %>%
    summarise(amount = sum(amount), min_posting_date = min(posting_date)) %>%
    ungroup() %>%
    filter ( amount < -0.06 | amount >  0.06) %>%
    mutate( age = date - min_posting_date) %>%
    filter ( age > 365)

  open_asset_data <- open_asset_data %>%
    group_by (company, company_name, company_input , date) %>%
    summarise(open_amount = sum(amount)) %>%
    ungroup()

  # aggregate at company level
  all_asset_data <- data %>%
    group_by (company, company_name, company_input , date) %>%
    summarise ( amount = sum(amount)) %>%
    ungroup()


  asset_data <- all_asset_data %>%
    left_join(open_asset_data, by = c('company', 'company_name', 'company_input', 'date')) %>%
    mutate(aging = round(100*open_amount/amount, 2))

  # filter on Company
  asset_data <- asset_data %>%
    filter ( company == .company)

  asset_data <- asset_data %>%
    filter(date <= last_date)

  attr(asset_data, 'company') <- .company

  #return
  asset_data
}


#---------------------------------------------------------------------------------#
#' plot aging data
#---------------------------------------------------------------------------------#
#' @export
#' @examples
#'  company_data <- load_company_region ("~/data/icrmkpi/iwo/", 'company-region.csv')
#'  data <- load_aging_all("~/data/icrmkpi/aging/", company_data = company_data)
#' asset_data <- prepare_aging(data,.company = 'G048', .year = 2020, .month = 3, rolling = 13)
#' plot_aging (data = asset_data)
plot_aging <- function(data ) {


  .company <- attr(data, 'company')
  .label <- paste(year(data$date), month(data$date), sep = '-')
  .break <- as.numeric(as.factor(data$date))



     data <- data %>%
     mutate(date_int = as.numeric(as.factor(date)))

  pl <- ggplot(data) +
    #geom_line( aes(date_int, aging), color = 'navyblue') +
    geom_bar( aes(date_int, aging), stat = 'identity', color = 'navyblue', fill = 'navyblue') +
    theme_gray () +
    theme(text = element_text(size=20)) +
    xlab( 'Month') + ylab('Aging %')  +
    #scale_x_date(date_labels="%b %y",date_breaks  ="1 month")+
    scale_x_continuous(breaks = .break, labels=.label) +
    ggtitle(data$company_name)

  return(pl)


}