Preparing Rmarkdown Document
# clear-up the environment
rm(list = ls())
# chunk options
knitr::opts_chunk$set(
message = FALSE,
warning = FALSE,
fig.align = "center",
comment = "#>"
)
# scientific notation
options(scipen = 9999)Read data from sql server (local) using RODBC library
local_conn = odbcConnect("local")
claim <- sqlQuery(local_conn,"SELECT * from lbb_algoritma.dbo.view_claim")Check if data loaded successfuly and assigned to object
head(claim)Discover Data Structure
glimpse(claim)#> Observations: 11,186
#> Variables: 53
#> $ id <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, ...
#> $ id_klaim <dbl> 2017, 2017, 2017, 2017, 2017, 2...
#> $ id_peserta <dbl> 2016.008, 2016.009, 2016.007, 2...
#> $ start_date <dttm> 2016-12-07, 2016-12-19, 2016-1...
#> $ end_date <dttm> 2017-10-07, 2017-10-19, 2017-0...
#> $ date_of_birth <dttm> 1995-11-30, 1993-12-20, 1984-0...
#> $ currency <fct> IDR, IDR, IDR, IDR, IDR, IDR, I...
#> $ sum_insured <dbl> 3600000, 1799000, 1798000, 2698...
#> $ participant_status <fct> Maturity, Maturity, Maturity, M...
#> $ sales_room_city_external <fct> NA, NA, NA, NA, Surabaya, NA, T...
#> $ region <fct> Unmapped, Unmapped, Unmapped, U...
#> $ category <fct> Unmapped, Unmapped, Unmapped, U...
#> $ claim_type <fct> Hospitalized, Hospitalized, Dea...
#> $ event_date <dttm> 2016-12-24, 2016-12-22, 2016-1...
#> $ register_date <dttm> 2017-01-10, 2017-01-25, 2017-0...
#> $ complete_doc_date <dttm> 2017-01-10 13:40:28, 2017-01-2...
#> $ verify_date <dttm> 2017-02-08 11:47:56, 2017-01-2...
#> $ decision_date <dttm> 2017-02-08 11:48:04, 2017-02-0...
#> $ payment_date <dttm> 2017-01-23, NA, 2017-02-27, NA...
#> $ claim_status <fct> Paid, Claim Rejected, Paid, Cla...
#> $ claim_amount <dbl> 454200, 233400, 1899497, 320300...
#> $ paid_amount <dbl> 454200, 0, 1899497, 0, 0, 10271...
#> $ reject_reason <fct> Non, Out of policy coverage, No...
#> $ aging_register_to_complete_doc <int> 0, 0, 0, 1, 0, 0, 1, 2, 1, 5, 4...
#> $ aging_complete_doc_to_verify <int> 21, 0, 5, 0, 0, 14, 2, 1, 10, 1...
#> $ aging_verify_to_decision <int> 0, 10, 1, 1, 1, 0, 1, 3, 0, 1, ...
#> $ aging_complete_doc_to_decision <int> 21, 10, 6, 1, 1, 14, 3, 4, 10, ...
#> $ aging_decision_to_payment <int> 0, 0, 2, 0, 0, 8, 0, 0, 5, 3, 2...
#> $ aging_register_to_payment <int> 21, 10, 8, 2, 1, 22, 4, 6, 16, ...
#> $ aging_complete_doc_to_payment <int> 21, 0, 8, 0, 0, 22, 0, 0, 15, 1...
#> $ sla_register_to_complete_doc <int> 90, 90, 90, 90, 90, 90, 90, 90,...
#> $ sla_complete_doc_to_verify <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1...
#> $ sla_verify_to_decision <int> 11, 11, 11, 11, 11, 11, 11, 11,...
#> $ sla_complete_doc_to_decision <int> 12, 12, 12, 12, 12, 12, 12, 12,...
#> $ sla_decision_to_payment <int> 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2...
#> $ sla_register_to_payment <int> 104, 104, 104, 104, 104, 104, 1...
#> $ sla_complete_doc_to_payment <int> 14, 14, 14, 14, 14, 14, 14, 14,...
#> $ status_sla_register_to_complete_doc <fct> Meet SLA, Meet SLA, Meet SLA, M...
#> $ status_sla_complete_doc_to_verify <fct> Below Expectation, Meet SLA, Be...
#> $ status_sla_verify_to_decision <fct> Meet SLA, Meet SLA, Meet SLA, M...
#> $ status_sla_complete_doc_to_decision <fct> Below Expectation, Meet SLA, Me...
#> $ status_sla_decision_to_payment <fct> Meet SLA, Meet SLA, Meet SLA, M...
#> $ status_sla_register_to_payment <fct> Meet SLA, Meet SLA, Meet SLA, M...
#> $ status_sla_complete_doc_to_payment <fct> Below Expectation, Meet SLA, Me...
#> $ meet_sla_register_to_complete_doc <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1...
#> $ below_sla_register_to_complete_doc <int> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0...
#> $ meet_sla_complete_doc_to_payment <int> 0, 1, 1, 1, 1, 0, 1, 1, 0, 1, 0...
#> $ below_sla_complete_doc_to_payment <int> 1, 0, 0, 0, 0, 1, 0, 0, 1, 0, 1...
#> $ meet_sla_complete_doc_to_decision <int> 0, 1, 1, 1, 1, 0, 1, 1, 1, 1, 0...
#> $ below_sla_complete_doc_to_decision <int> 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 1...
#> $ meet_sla_decision_to_payment <int> 1, 1, 1, 1, 1, 0, 1, 1, 0, 0, 1...
#> $ below_sla_decision_to_payment <int> 0, 0, 0, 0, 0, 1, 0, 0, 1, 1, 0...
#> $ diagnosa <fct> "Injuries to the abdomen, lower...Variable descriptions:
id: Running number atau nomor urutid_klaim: Id transaksi klaimid_peserta: Id unique pesertastart_date: Tanggal mulai peserta dalam asuransiend_date: Tanggal selesai peserta dalam asuransidate_of_birth: Tanggal lahir pesertacurrency: Mata uangsum_insured: Uang pertanggunganparticipant_status: Status kepesertaan asuransisales_room_city_external: Kota pada saat peserta membeli asuransiregion: Klasifikasi berdasarkan kota domisili pesertacategory: Klasifikasi berdasarkan media penjualan asuransiclaim_type: Tipe klaimevent_date: Tanggal Kejadianregister_date: Tanggal pengajuan klaimcomplete_doc_date: Tanggal kelengkapan dokumen klaimverify_date: Tanggal klaim diverifikasidecision_date: Tanggal klaim diputuskan untuk disetujui atau ditolakpayment_date: Tanggal klaim dibayarclaim_status: Status klaimclaim_amount: Jumlah klaim yang diajukanpaid_amount: Jumlah klaim yang dibayarreject_reason: Alasan klaim ditolakaging_register_to_complete_doc: Selisih hari dari tanggal pengajuan klaim ke tanggal dokumen lengkapaging_complete_doc_to_verify: Selisih hari dari tanggal dokumen lengkap ke tanggal verifikasi klaimaging_verify_to_decision: Selisih hari dari tanggal verifikasi klaim ke tanggal decisionaging_complete_doc_to_decision: Selisih hari dari tanggal dokumen lengkap ke tanggal decisionaging_decision_to_payment: Selisih hari dari tanggal decision ke tanggal klaim dibayaraging_register_to_payment: Selisih hari dari tanggal pengajuan klaim ke tanggal klaim dibayaraging_complete_doc_to_payment: Selisih hari dari tanggal dokumen lengkap ke tanggal klaim dibayarsla_register_to_complete_doc: Sla dari tanggal pengajuan klaim ke tanggal dokumen lengkapsla_complete_doc_to_verify: Sla dari tanggal dokumen lengkap ke tanggal verifikasi klaimsla_verify_to_decision: Sla dari tanggal verifikasi klaim ke tanggal decisionsla_complete_doc_to_decision: Sla dari tanggal dokumen lengkap ke tanggal decisionsla_decision_to_payment: Sla dari tanggal decision ke tanggal klaim dibayarsla_register_to_payment: Sla dari tanggal pengajuan klaim ke tanggal klaim dibayarsla_complete_doc_to_payment: Sla dari tanggal dokumen lengkap ke tanggal klaim dibayarstatus_sla_register_to_complete_doc: Meet SLA/Below Expectation untuk tanggal pengajuan klaim ke tanggal dokumen lengkapstatus_sla_complete_doc_to_verify: Meet SLA/Below Expectation untuk tanggal dokumen lengkap ke tanggal verifikasi klaimstatus_sla_verify_to_decision: Meet SLA/Below Expectation untuk tanggal verifikasi klaim ke tanggal decisionstatus_sla_complete_doc_to_decision: Meet SLA/Below Expectation untuk tanggal dokumen lengkap ke tanggal decisionstatus_sla_decision_to_payment: Meet SLA/Below Expectation untuk tanggal decision ke tanggal klaim dibayarstatus_sla_register_to_payment: Meet SLA/Below Expectation untuk tanggal pengajuan klaim ke tanggal klaim dibayarstatus_sla_complete_doc_to_payment: Meet SLA/Below Expectation untuk tanggal dokumen lengkap ke tanggal klaim dibayarmeet_sla_register_to_complete_doc: Jika Meet SLA = 1, Below Expectation = 0 untuk selisih tanggal pengajuan klaim ke tanggal dokumen lengkapbelow_sla_register_to_complete_doc: Jika Below Expectation = 1, Meet SLA = 0 untuk selisih tanggal pengajuan klaim ke tanggal dokumen lengkapmeet_sla_complete_doc_to_payment: Jika Meet SLA = 1, Below Expectation = 0 untuk selisih tanggal dokumen lengkap ke tanggal klaim dibayarbelow_sla_complete_doc_to_payment: Jika Below Expectation = 1, Meet SLA = 0 untuk selisih tanggal dokumen lengkap ke tanggal klaim dibayarmeet_sla_complete_doc_to_decision: Jika Meet SLA = 1, Below Expectation = 0 untuk selisih tanggal dokumen lengkap ke tanggal decisionbelow_sla_complete_doc_to_decision: Jika Below Expectation = 1, Meet SLA = 0 untuk selisih tanggal dokumen lengkap ke tanggal decisionmeet_sla_decision_to_payment: Jika Meet SLA = 1, Below Expectation = 0 untuk selisih tanggal decision ke tanggal klaim dibayarbelow_sla_decision_to_payment: Jika Below Expectation = 1, Meet SLA = 0 untuk selisih tanggal decision ke tanggal klaim dibayarChecking some missing value
claim_data <- claim %>%
is.na() %>%
colSums()
claim_data#> id id_klaim
#> 0 0
#> id_peserta start_date
#> 0 0
#> end_date date_of_birth
#> 0 0
#> currency sum_insured
#> 0 0
#> participant_status sales_room_city_external
#> 0 21
#> region category
#> 0 0
#> claim_type event_date
#> 0 0
#> register_date complete_doc_date
#> 0 0
#> verify_date decision_date
#> 0 66
#> payment_date claim_status
#> 607 0
#> claim_amount paid_amount
#> 0 0
#> reject_reason aging_register_to_complete_doc
#> 67 0
#> aging_complete_doc_to_verify aging_verify_to_decision
#> 0 0
#> aging_complete_doc_to_decision aging_decision_to_payment
#> 0 0
#> aging_register_to_payment aging_complete_doc_to_payment
#> 0 0
#> sla_register_to_complete_doc sla_complete_doc_to_verify
#> 0 0
#> sla_verify_to_decision sla_complete_doc_to_decision
#> 0 0
#> sla_decision_to_payment sla_register_to_payment
#> 0 0
#> sla_complete_doc_to_payment status_sla_register_to_complete_doc
#> 0 0
#> status_sla_complete_doc_to_verify status_sla_verify_to_decision
#> 0 0
#> status_sla_complete_doc_to_decision status_sla_decision_to_payment
#> 0 0
#> status_sla_register_to_payment status_sla_complete_doc_to_payment
#> 0 0
#> meet_sla_register_to_complete_doc below_sla_register_to_complete_doc
#> 0 0
#> meet_sla_complete_doc_to_payment below_sla_complete_doc_to_payment
#> 0 0
#> meet_sla_complete_doc_to_decision below_sla_complete_doc_to_decision
#> 0 0
#> meet_sla_decision_to_payment below_sla_decision_to_payment
#> 0 0
#> diagnosa
#> 0claim$Y_register_date <- year(claim$register_date)
claim$M_register_date <- month(claim$register_date, label = T, abbr = T)
claim$m_register_date <- month(claim$register_date)
claim$YYYY_mm_register_date <- format(as.Date(claim$register_date, "%Y-%m-%d"), "%Y-%m")
claim$MM_YYYY_register_date <- paste(claim$M_register_date, claim$Y_register_date, sep="-")
claim$YQ_register_date <- quarter(claim$register_date, with_year = TRUE)theme_iman <- theme(legend.key = element_rect(fill="black"),
legend.background = element_rect(color="white", fill="#263238"),
plot.subtitle = element_text(size=6, color="white"),
panel.background = element_rect(fill="#dddddd"),
panel.border = element_rect(fill=NA),
panel.grid.minor.x = element_blank(),
panel.grid.major.x = element_blank(),
panel.grid.major.y = element_line(color="darkgrey", linetype=2),
panel.grid.minor.y = element_blank(),
plot.background = element_rect(fill="#263238"),
text = element_text(color="white"),
axis.text = element_text(color="white"),
legend.position='none')claim_number_by_type <-
claim %>%
group_by(claim_type, YQ_register_date) %>%
summarise(total_claim_number = n()) %>%
ungroup() %>%
mutate(text = glue(
"Claim Type: {claim_type}
Year & Quarter: {YQ_register_date}
Total Claim Number: {total_claim_number}"
))plot_claim_number_by_claim_type <- ggplot(data = claim_number_by_type, aes(x = YQ_register_date,
y = total_claim_number)) +
geom_point(aes(color = claim_type, text = text), show.legend = F) +
geom_line(aes(color = claim_type), show.legend = F)+
labs(title = "Trend Total Claim Number Based on Claim Type",
y = "Total Claim Number",
x = "Quarter & Year Register Date") +
theme_iman
ggplotly(plot_claim_number_by_claim_type, tooltip = "text") %>%
config(displaylogo = F)claim_amount_by_type <-
claim %>%
group_by(claim_type, YQ_register_date) %>%
summarise(total_claim_amount = sum(claim_amount)) %>%
ungroup() %>%
mutate(text = glue(
"Claim Type: {claim_type}
Year & Quarter: {YQ_register_date}
Total Claim Amount: {total_claim_amount}"
)) plot_claim_amount_by_claim_type <- ggplot(data = claim_amount_by_type, aes(x = YQ_register_date,
y = total_claim_amount)) +
geom_point(aes(color = claim_type, text = text), show.legend = F) +
geom_line(aes(color = claim_type), show.legend = F)+
labs(title = "Trend Total Claim Amount Based on Claim Type",
y = "Total Claim Amount",
x = "Quarter & Year Register Date") +
scale_y_continuous(labels = unit_format(unit = "M", scale = 1e-6))+
theme_iman
ggplotly(plot_claim_amount_by_claim_type, tooltip = "text") %>%
config(displaylogo = F)claim_number_by_status <-
claim %>%
group_by(claim_status, Y_register_date) %>%
summarise(total_claim_number = n()) %>%
ungroup() %>%
mutate(text = glue(
"Claim Status: {claim_status}
Year: {Y_register_date}
Total Claim Number: {total_claim_number}"
))plot_claim_number_by_claim_status <-
ggplot(data = claim_number_by_status, aes(x = Y_register_date,
y = total_claim_number,
text = text)) +
geom_col(aes(fill = claim_status), position = "dodge", show.legend = F)+
labs(title = "Trend Total Claim Number Based on Claim Status",
y = "Total Claim Number",
x = "Year Register Date") +
theme_iman
ggplotly(plot_claim_number_by_claim_status, tooltip = "text")claim_amount_by_status <-
claim %>%
group_by(claim_status, Y_register_date) %>%
summarise(total_claim_amount = sum(claim_amount)) %>%
ungroup() %>%
mutate(text = glue(
"Claim Status: {claim_status}
Year: {Y_register_date}
Total Claim Amount: {total_claim_amount}"
))plot_claim_amount_by_claim_status <-
ggplot(data = claim_amount_by_status, aes(x = Y_register_date,
y = total_claim_amount,
text = text)) +
geom_col(aes(fill = claim_status), position = "dodge", show.legend = F)+
labs(title = "Trend Total Claim Amount Based on Claim Status",
y = "Total Claim Amount",
x = "Year Register Date") +
scale_y_continuous(labels = unit_format(unit = "M", scale = 1e-6))+
theme_iman
ggplotly(plot_claim_amount_by_claim_status, tooltip = "text")claim_number_by_region <-
claim %>%
group_by(region, YQ_register_date) %>%
summarise(total_claim_number = n()) %>%
ungroup() %>%
mutate(text = glue(
"Region: {region}
Year & Quarter: {YQ_register_date}
Total Claim Number: {total_claim_number}"
))plot_claim_number_by_region <- ggplot(data = claim_number_by_region, aes(x = YQ_register_date,
y = total_claim_number)) +
geom_point(aes(color = region, text = text), show.legend = F) +
geom_line(aes(color = region), show.legend = F)+
labs(title = "Trend Total Claim Number Based on Region",
y = "Total Claim Number",
x = "Quarter & Year Register Date") +
theme_iman
ggplotly(plot_claim_number_by_region, tooltip = "text") %>%
config(displaylogo = F)claim_amount_by_region <-
claim %>%
group_by(region, YQ_register_date) %>%
summarise(total_claim_amount = sum(claim_amount)) %>%
ungroup() %>%
mutate(text = glue(
"Region: {region}
Year & Quarter: {YQ_register_date}
Total Claim Amount: {total_claim_amount}"
))plot_claim_amount_by_region <- ggplot(data = claim_amount_by_region, aes(x = YQ_register_date,
y = total_claim_amount)) +
geom_point(aes(color = region, text = text), show.legend = F) +
geom_line(aes(color = region), show.legend = F)+
labs(title = "Trend Total Claim Amount Based on Region",
y = "Total Claim Amount",
x = "Quarter & Year Register Date") +
scale_y_continuous(labels = unit_format(unit = "M", scale = 1e-6))+
theme_iman
ggplotly(plot_claim_amount_by_region, tooltip = "text") %>%
config(displaylogo = F)claim_number_by_category <-
claim %>%
group_by(category, YQ_register_date) %>%
summarise(total_claim_number = n()) %>%
ungroup() %>%
mutate(text = glue(
"Category: {category}
Year & Quarter: {YQ_register_date}
Total Claim Number: {total_claim_number}"
))plot_claim_number_by_category <- ggplot(data = claim_number_by_category, aes(x = YQ_register_date,
y = total_claim_number)) +
geom_point(aes(color = category, text = text), show.legend = F) +
geom_line(aes(color = category), show.legend = F)+
labs(title = "Trend Total Claim Number Based on Category",
y = "Total Claim Number",
x = "Quarter & Year Register Date") +
theme_iman
ggplotly(plot_claim_number_by_category, tooltip = "text") %>%
config(displaylogo = F)claim_amount_by_category <-
claim %>%
group_by(category, YQ_register_date) %>%
summarise(total_claim_amount = sum(claim_amount)) %>%
ungroup() %>%
mutate(text = glue(
"Category: {category}
Year & Quarter: {YQ_register_date}
Total Claim Amount: {total_claim_amount}"
))plot_claim_amount_by_category <- ggplot(data = claim_amount_by_category, aes(x = YQ_register_date,
y = total_claim_amount)) +
geom_point(aes(color = category, text = text), show.legend = F) +
geom_line(aes(color = category), show.legend = F)+
labs(title = "Trend Total Claim Amount Based on Category",
y = "Total Claim Amount",
x = "Quarter & Year Register Date") +
scale_y_continuous(labels = unit_format(unit = "M", scale = 1e-6))+
theme_iman
ggplotly(plot_claim_amount_by_category, tooltip = "text") %>%
config(displaylogo = F)claim_sla_complete_doc_to_payment <-
claim %>%
filter(claim_status == "Paid") %>%
group_by(
YYYY_mm_register_date,
status_sla_complete_doc_to_payment) %>%
summarise(
count_status_sla_complete_doc_to_payment =
sum(meet_sla_complete_doc_to_payment)+
sum(below_sla_complete_doc_to_payment)) %>%
ungroup() %>%
arrange(desc(YYYY_mm_register_date)) %>%
mutate(text = glue(
"SLA Status: {status_sla_complete_doc_to_payment}
Register Date: {YYYY_mm_register_date}
Total Claim Number: {count_status_sla_complete_doc_to_payment}"
)) plot_claim_sla_complete_doc_to_payment <-
ggplot(data = claim_sla_complete_doc_to_payment,
aes( x = YYYY_mm_register_date,
y = count_status_sla_complete_doc_to_payment,
text = text)) +
geom_col(aes(fill = status_sla_complete_doc_to_payment), position = "fill", show.legend = F)+
labs(title = "SLA Performance - Complete Doc to Payment",
y = "Claim Number",
x = "Register Date")+
coord_flip()+
theme_iman
ggplotly(plot_claim_sla_complete_doc_to_payment, tooltip = "text")#karena data di bawah Juli 2018 menggunakan SLA 14 hari maka data difilter hanya untuk yang menggunakan SLA 7 hari yaitu klaim dengan tanggal pengajuan di atas Juni 2018
SLA_x <- 2 #parameter input SLA ini silahkan diganti-ganti, jika ingin tahu bagaimana SLA performance jika SLA diturunkan
claim_sla_x <-
claim %>%
filter(claim_status == "Paid", register_date >= "2018-07-01") %>%
mutate(
status_sla_complete_doc_to_payment_new =
case_when(aging_complete_doc_to_payment > SLA_x ~ "Below Expectation",
TRUE ~ "Meet SLA")) %>%
select(
YYYY_mm_register_date,
status_sla_complete_doc_to_payment_new)
claim_sla_x_ready <-
claim_sla_x %>%
mutate(
meet_sla_complete_doc_to_payment_new =
case_when(status_sla_complete_doc_to_payment_new != 'Meet SLA' ~ 0, TRUE ~ 1),
below_sla_complete_doc_to_payment_new =
case_when(status_sla_complete_doc_to_payment_new != 'Below Expectation' ~ 0, TRUE ~ 1)
)
claim_sla_complete_doc_to_payment_x <-
claim_sla_x_ready %>%
group_by(
YYYY_mm_register_date,
status_sla_complete_doc_to_payment_new) %>%
summarise(
count_status_sla_complete_doc_to_payment_new =
sum(meet_sla_complete_doc_to_payment_new)+
sum(below_sla_complete_doc_to_payment_new)) %>%
ungroup() %>%
arrange(desc(YYYY_mm_register_date)) %>%
mutate(text = glue(
"SLA Status: {status_sla_complete_doc_to_payment_new}
Register Date: {YYYY_mm_register_date}
Total Claim Number: {count_status_sla_complete_doc_to_payment_new}"
)) plot_claim_sla_complete_doc_to_payment_new <-
ggplot(data = claim_sla_complete_doc_to_payment_x,
aes( x = YYYY_mm_register_date,
y = count_status_sla_complete_doc_to_payment_new,
text = text)) +
geom_col(aes(fill = status_sla_complete_doc_to_payment_new), position = "fill", show.legend = F)+
labs(title = "SLA Performance - Complete Doc to Payment",
y = "Claim Number",
x = "Register Date")+
coord_flip()+
theme_iman
ggplotly(plot_claim_sla_complete_doc_to_payment_new, tooltip = "text")