We are an insurance company that join with another financial technology company as business to business, we covered life and health our partner customer. Our partner challenged us to decrease our SLA of claim process and we have to got this challenged.
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)Data already stored in our SQL Server Read data from sql server (local) using RODBC library
#local_conn = odbcConnect("local")
#claim_database <- sqlQuery(local_conn,"SELECT * from lbb_algoritma.dbo.view_claim")
#write.csv(claim_database, "claim/data/claim.csv", sep = "|", row.names = FALSE, quote = FALSE)
claim <- read_csv("claim/data/claim.csv", quote = "|")Check if data loaded successfuly and assigned to object
head(claim)Discover Data Structure
glimpse(claim)#> Observations: 11,186
#> Variables: 53
#> $ id <dbl> 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 <date> 2016-12-07, 2016-12-19, 2016-1...
#> $ end_date <date> 2017-10-07, 2017-10-19, 2017-0...
#> $ date_of_birth <date> 1995-11-30, 1993-12-20, 1984-0...
#> $ currency <chr> "IDR", "IDR", "IDR", "IDR", "ID...
#> $ sum_insured <dbl> 3600000, 1799000, 1798000, 2698...
#> $ participant_status <chr> "Maturity", "Maturity", "Maturi...
#> $ sales_room_city_external <chr> NA, NA, NA, NA, "Surabaya", NA,...
#> $ region <chr> "Unmapped", "Unmapped", "Unmapp...
#> $ category <chr> "Unmapped", "Unmapped", "Unmapp...
#> $ claim_type <chr> "Hospitalized", "Hospitalized",...
#> $ event_date <date> 2016-12-24, 2016-12-22, 2016-1...
#> $ register_date <date> 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 <chr> "Paid", "Claim Rejected", "Paid...
#> $ claim_amount <dbl> 454200, 233400, 1899497, 320300...
#> $ paid_amount <dbl> 454200, 0, 1899497, 0, 0, 10271...
#> $ reject_reason <chr> "Non", "Out of policy coverage"...
#> $ aging_register_to_complete_doc <dbl> 0, 0, 0, 1, 0, 0, 1, 2, 1, 5, 4...
#> $ aging_complete_doc_to_verify <dbl> 21, 0, 5, 0, 0, 14, 2, 1, 10, 1...
#> $ aging_verify_to_decision <dbl> 0, 10, 1, 1, 1, 0, 1, 3, 0, 1, ...
#> $ aging_complete_doc_to_decision <dbl> 21, 10, 6, 1, 1, 14, 3, 4, 10, ...
#> $ aging_decision_to_payment <dbl> 0, 0, 2, 0, 0, 8, 0, 0, 5, 3, 2...
#> $ aging_register_to_payment <dbl> 21, 10, 8, 2, 1, 22, 4, 6, 16, ...
#> $ aging_complete_doc_to_payment <dbl> 21, 0, 8, 0, 0, 22, 0, 0, 15, 1...
#> $ sla_register_to_complete_doc <dbl> 90, 90, 90, 90, 90, 90, 90, 90,...
#> $ sla_complete_doc_to_verify <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1...
#> $ sla_verify_to_decision <dbl> 11, 11, 11, 11, 11, 11, 11, 11,...
#> $ sla_complete_doc_to_decision <dbl> 12, 12, 12, 12, 12, 12, 12, 12,...
#> $ sla_decision_to_payment <dbl> 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2...
#> $ sla_register_to_payment <dbl> 104, 104, 104, 104, 104, 104, 1...
#> $ sla_complete_doc_to_payment <dbl> 14, 14, 14, 14, 14, 14, 14, 14,...
#> $ status_sla_register_to_complete_doc <chr> "Meet SLA", "Meet SLA", "Meet S...
#> $ status_sla_complete_doc_to_verify <chr> "Below Expectation", "Meet SLA"...
#> $ status_sla_verify_to_decision <chr> "Meet SLA", "Meet SLA", "Meet S...
#> $ status_sla_complete_doc_to_decision <chr> "Below Expectation", "Meet SLA"...
#> $ status_sla_decision_to_payment <chr> "Meet SLA", "Meet SLA", "Meet S...
#> $ status_sla_register_to_payment <chr> "Meet SLA", "Meet SLA", "Meet S...
#> $ status_sla_complete_doc_to_payment <chr> "Below Expectation", "Meet SLA"...
#> $ meet_sla_register_to_complete_doc <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1...
#> $ below_sla_register_to_complete_doc <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0...
#> $ meet_sla_complete_doc_to_payment <dbl> 0, 1, 1, 1, 1, 0, 1, 1, 0, 1, 0...
#> $ below_sla_complete_doc_to_payment <dbl> 1, 0, 0, 0, 0, 1, 0, 0, 1, 0, 1...
#> $ meet_sla_complete_doc_to_decision <dbl> 0, 1, 1, 1, 1, 0, 1, 1, 1, 1, 0...
#> $ below_sla_complete_doc_to_decision <dbl> 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 1...
#> $ meet_sla_decision_to_payment <dbl> 1, 1, 1, 1, 1, 0, 1, 1, 0, 0, 1...
#> $ below_sla_decision_to_payment <dbl> 0, 0, 0, 0, 0, 1, 0, 0, 1, 1, 0...
#> $ diagnosa <chr> "Injuries to the abdomen", "OTH...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
#> 0theme_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$Y_register_date <- lubridate::year(claim$register_date)
claim$M_register_date <- lubridate::month(claim$register_date, label = T, abbr = T)
claim$m_register_date <- lubridate::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 <- lubridate::quarter(claim$register_date, with_year = TRUE)yoy_by_claim_number <- claim %>%
group_by(Y_register_date, M_register_date) %>%
summarise(total_claim_number = n()) %>%
ungroup() %>%
mutate(text = glue(
"Register Date: {M_register_date} - {Y_register_date}
Total Claim Number: {total_claim_number}"
))
yoy_by_claim_numberplot_yoy_by_claim_number <- ggplot(data = yoy_by_claim_number,
aes(fill=M_register_date,
y=total_claim_number,
x=interaction(Y_register_date, M_register_date))) +
geom_segment(aes(x=interaction(Y_register_date, M_register_date),
xend=interaction(Y_register_date, M_register_date),
y=0,
yend=total_claim_number),
color="grey")+
geom_point(aes(col = M_register_date, size = 4, text = text)) +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
ggtitle("Year on Year by Claim Number") +
labs(y = "Total Claim Number",
x = "Year & Month of Register Date")
ggplotly(plot_yoy_by_claim_number, tooltip = "text") %>%
config(displaylogo = F) %>%
layout(showlegend = FALSE)yoy_by_claim_amount <- claim %>%
group_by(Y_register_date, M_register_date) %>%
summarise(total_claim_amount = sum(claim_amount)) %>%
ungroup() %>%
mutate(text = glue(
"Register Date: {M_register_date} - {Y_register_date}
Total Claim Amount: {total_claim_amount}"
))
yoy_by_claim_amountplot_yoy_by_claim_amount <- ggplot(data = yoy_by_claim_amount,
aes(fill=M_register_date,
y=total_claim_amount,
x=interaction(Y_register_date, M_register_date))) +
geom_segment(aes(x=interaction(Y_register_date, M_register_date),
xend=interaction(Y_register_date, M_register_date),
y=0,
yend=total_claim_amount),
color="grey")+
geom_point(aes(col = M_register_date, size = 4, text = text)) +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
scale_y_continuous(labels = unit_format(unit = "M", scale = 1e-6)) +
ggtitle("Year on Year by Claim Amount") +
labs(y = "Total Claim Amount",
x = "Year & Month of Register Date")
ggplotly(plot_yoy_by_claim_amount, tooltip = "text") %>%
config(displaylogo = F) %>%
layout(showlegend = FALSE)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() %>%
mutate(count_status = ifelse(status_sla_complete_doc_to_payment == "Meet SLA",
count_status_sla_complete_doc_to_payment,
-1*count_status_sla_complete_doc_to_payment)) %>%
arrange(desc(YYYY_mm_register_date)) %>%
mutate(text = glue(
"SLA Status: {status_sla_complete_doc_to_payment}
Register Date: {YYYY_mm_register_date}
Claim Number: {count_status_sla_complete_doc_to_payment}"
))breaks_values <- pretty(claim_sla_complete_doc_to_payment$count_status)
plot_yoy_by_claim_amount <- ggplot(data = claim_sla_complete_doc_to_payment,
aes(x = YYYY_mm_register_date,
y = count_status,
fill = status_sla_complete_doc_to_payment))+
geom_hline(yintercept = 0)+
geom_bar(stat = "identity")+
geom_point(aes(text = text)) +
coord_flip()+
scale_y_continuous(breaks = breaks_values,
labels = abs(breaks_values))+
theme_minimal() +
ggtitle("SLA Performance") +
labs(y = "SLA Status",
x = "Year & Month of Register Date")
ggplotly(plot_yoy_by_claim_amount, tooltip = "text") %>%
config(displaylogo = F) %>%
layout(showlegend = F)Our partner want to see how many claim number based on claim type ### Subseting
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)Our partner want to see how many claim amount based on claim type ### Subseting
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)Our partner want to see how many claim number based on claim status ### Subseting
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")Our partner want to see how many claim amount based on claim status ### Subseting
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")Our partner want to see how many claim number based on region ### Subseting
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)Our partner want to see how many claim amount based on region ### Subseting
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)Our partner want to see how many claim number based on category ### Subseting
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)Our partner want to see how many claim amount based on category ### Subseting
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")