Audit Data Analitycs dengan R – Identifikasi Tanggal Kegiatan Tumpang Tindih

Galibnya pelaku perjalanan bisnis hanya boleh mengklaim satu kegiatan di satu tanggal. Auditor perlu menguji ketaatan penikmat uang jalan (-jalan) namun data terlalu banyak sehingga menyulitkan pengujian, langsung meminta dokumen bukti perjalanan juga seringnya berakhir jadi sia-sia. Bagaimana teknologi dapat membantu auditor melakukan prosedur audit pengeluaran perjalanan, akan kita bahas dalam tulisan ini.

kode lima detik

library(tidyverse)
library(xlsx)

df <- readxl::read_excel('folder//basangdata_date_overlap.xlsx') %>% 
  mutate(No = as.integer(No),
         Start = as.Date(Start),
         End = as.Date(End))

f <- function(x) {
  ret <- 0
  
  current_no <- as.integer(x['No'])
  current_ID <- x['ID_Person']
  current_start <- x['Start']
  current_end <- x['End']
  
  temp_df <- df %>%
    filter(
      ID_Person == current_ID,
      No != current_no
    )
  
  if (nrow(temp_df) > 0) {
    for (row in 1:nrow(temp_df)) {
      if (
        (current_start <= temp_df[[row, 'End']]) & 
        (current_end >= temp_df[[row, 'Start']])) {
        ret <- 1
      }
    }
  }
  
  return(ret)
}

df$is_overlap <- apply(df, 1, f)

df_overlap <- df %>% 
  filter(is_overlap == 1) %>% 
  arrange(ID_Person, Start)

df_overlap %>% write.xlsx('df_overlap.xlsx')

---

Tujuan

Dalam tulisan ini kita akan belajar:

• Membaca Excel File
• Bagaimana Menguji Apakah Suatu Periode Melewati Periode Lainnya
• Fitur apply pada R
• Membuat Fungsi pada R

Urgensi

Salah satu gula-gula bagi pekerja adalah perjalanan yang dibiayai kantor. Bertemu orang baru, melewati jalanan asing, mengunjungi tempat yang selama ini hanya diketahui lewat bacaan dan bermacam pengalaman sensoris lainnya.

Tak heran perjalanan bisnis menjadi salah satu insentif yang disukai pekerja, apalagi di bagian mendapat sangunya.

Adalah kebijakan yang umum untuk membatasi frekuensi perjalanan dinas, salah satu alasan berterima umum adalah kurangnya sumberdaya yang dimiliki sehingga tidak memungkinkan dilaksanakan perjalanan kerja yang tak terbatas.

Prosedur audit atas biaya perjalanan meminta auditor untuk menguji dokumen bukti belanja yang dilakukan pelaku perjalanan. Namun kegiatan yang melibatkan pemeriksaan dokumen dapat memakan waktu dan sangat melelahkan.

Langsung memeriksa dokumen juga berpotensi “salah sasaran”, transaksi yang sah terambil sebagai sampel sementara transaksi fraud atau salah saji yang material malah tak terdeteksi.

Jika sudah punya data perjalanan bisnis kita dapat lebih dulu melakukan pengujian pada data. Salah satu yang “mudah” adalah menguji apakah terdapat periode perjalanan yang tumpang tindih/overlap dengan periode lainnya.

---

Studi Kasus

Kita akan belajar melakukan pengujian untuk memastikan seorang pegawai hanya mengikuti (atau mengklaim pembayaran) satu kegiatan dalam satu periode waktu.

Kita punya data bersahaja yang terdiri atas 30 baris pengeluaran perjalanan dinas yang diklaim oleh pegawai.

Data dalam bentuk Excel File dapat diunduh di sini. Sebagai catatan nama (ID_Person) pada data adalah nama fiktif yang dibuat oleh ninjaname. Nama kegiatan dan tanggal juga tak kalah fiktifnya.

Secara visual, dapatkah pembaca mengetahui siapa dan kegiatan apa saja yang pelaksanaannya bersamaan dengan kegiatan lain? Atau semua data sudah oke?

Penulis, yang adalah pembuat data, sendiri kesulitan mengenali kegiatan mana saja yang waktunya bertabrakan dengan kegiatan lain. Meski baru 30 baris data yang harus diuji.

Lumayan membantu saat masing-masing nama difilter kemudian tanggal dibandingkan. Namun sepertinya masih merupakan pekerjaan yang membutuhkan ketelitian tinggi dan bergantung sekali pada kesehatan mata (dan pikiran).

Kita perlu metode yang lebih mudah dan akurat untuk memastikan pelaku perjalanan tidak mengambil (uang perjalanan) lebih banyak dari yang seharusnya.

Kondisi yang Harus Dipenuhi oleh Data

Sebelum melangkah lebih lanjut, perlu diungkapkan terlebih dahulu syarat rukun yang harus dipenuhi oleh data agar dapat diproses.

Minimal 3 Kolom

Terdiri dari kolom:

• Nama/ID
• Tanggal Berangkat/Mulai/Pergi
• Tanggal Kembali/Selesai/Pulang

Kolom Nama

Terdiri dari Kolom Nama/ID atau apapun yang dapat digunakan untuk mengidentifikasi pelaku perjalanan bisnis.

Kolom Tanggal Mulai dan Tanggal Kembali dalam Format/Bentuk Tanggal

Karena data utama yang perlu dibandingkan adalah tanggal mulai dan kembali maka kolom tersebut harus dalam bentuk (format) tanggal yang memadai.

Jika tanggal tidak hadir dalam bentuk yang memadai maka kita akan mendapati berbagai masalah, yang akan dibahas akibat dan solusinya di bagian akhir tulisan ini, Permasalahan Ditemui.

Kolom yang Sebaiknya ada pada Data

Syarat 3 kolom adalah mutlak harus dipenuhi agar data dapat diproses namun di luar itu terdapat 2 kolom tambahan yang keberadaannya akan memudahkan proses pengujian.

Kolom Nama Kegiatan

Keberadaannya akan memudahkan saat melakukan konfirmasi.

Sebagai misal jika kita bertanya kepada pelaku perjalanan, “Pak Ario Hendy kegiatan pada tanggal 1 sampai 3 Februari bertabrakan dengan kegiatan pada tanggal 3 Februari.” Tentu lebih sulit dicerna ketimbang, “Pak Ario Hendy, kegiatan C dan kegiatan E tanggalnya bertabrakan.”

Kolom Nomor Urut

Kolom ini dapat diganti dengan kolom ID transaksi, atau apapun yang dapat membedakan antara satu baris data dengan baris data lainnya.

Pada saat membuat kode kita akan sangat terbantu dengan keberadaan kolom ini karena itu sangat disarankan adanya. Pun tidak ada, kita dapat dengan mudah membuat sendiri sebuah kolom berisi nomor urut.

Metode

Disarikan dari Stackoverflow, untuk dapat mengenali apakah periode A overlap dengan periode B maka kondisi berikut harus terjadi.

Sebagai pengingat, logical operator AND akan menghasilkan output sebagai berikut.

Agar Kegiatan A pada Periode A bertabrakan dengan Kegiatan B pada Periode B (Hasil_AND adalah TRUE) maka kondisi pertama (berwarna biru) dan kondisi kedua (berwarna magenta) harus sama-sama benar (TRUE).

Jika salah satu saja kondisi bernilai FALSE maka kedua periode tersebut tidak bertabrakan.

Ilustrasi

Untuk mencoba formula di atas, akan digunakan tanggal kegiatan yang diikuti Ario Hendy.

Kegiatan A vs Kegiatan C

Jika tanggal pada dua kegiatan di atas kita masukkan pada rumus, maka hasilnya adalah False/Salah/Tidak benar jika dikatakan Kegiatan A bertabrakan dengan Kegiatan C.

Kegiatan A vs Kegiatan Lain

Proses pengujian di atas harusnya dilakukan pada semua kegiatan lain yang diikuti oleh Ario Hendy namun untuk meringkas tulisan, kita anggap semua telah dilaksanakan dan tidak ditemukan kegiatan lain yang bertabrakan dengan Kegiatan A.

Kegiatan C vs Kegiatan E

Kita mendapatkan kondisi dimana hasil akhir adalah True yang berarti Kegiatan C dan Kegiatan E berbagi tanggal yang sama, dalam hal ini sama-sama menggunakan tanggal 3 Februari 2021.

Kegiatan E vs Kegiatan G

Formula/Rumus/Metode yang digunakan telah benar mengenali Kegiatan E dan Kegiatan G sebagai tidak bertabrakan.

Karena formula yang digunakan telah teruji, sekarang waktunya menerjemahkan menjadi kode R.

Kode

library(tidyverse)
library(xlsx)

df <- readxl::read_excel('folder//basangdata_date_overlap.xlsx') %>% 
  mutate(No = as.integer(No),
         Start = as.Date(Start),
         End = as.Date(End))

f <- function(x) {
  ret <- 0
  
  current_no <- as.integer(x['No'])
  current_ID <- x['ID_Person']
  current_start <- x['Start']
  current_end <- x['End']
  
  temp_df <- df %>%
    filter(
      ID_Person == current_ID,
      No != current_no
    )
  
  if (nrow(temp_df) > 0) {
    for (row in 1:nrow(temp_df)) {
      if (
        (current_start <= temp_df[[row, 'End']]) & 
        (current_end >= temp_df[[row, 'Start']])) {
        ret <- 1
      }
    }
  }
  
  return(ret)
}

df$is_overlap <- apply(df, 1, f)

df_overlap <- df %>% 
  filter(is_overlap == 1) %>% 
  arrange(ID_Person, Start)

df_overlap %>% write.xlsx('df_overlap.xlsx')

Kode di atas akan menghasilkan sebuah Excel File bernama df_overlap.xlsx yang isinya kurang lebih seperti ini (tanpa mengurangi kesakralan, file tersebut telah melalui proses penyuntingan untuk mempermudah pengenalan visual).

Dari 30 perjalanan bisnis yang diklaim, 12 diantaranya menggunakan tanggal yang sama, cukup mencengangkan.

Di bawah ini kita akan bahas satu per satu langkah yang kita lakukan pada kode.

Load Data

library(tidyverse)
library(xlsx)

df <- readxl::read_excel('folder//basangdata_date_overlap.xlsx') %>% 
  mutate(No = as.integer(No),
         Start = as.Date(Start),
         End = as.Date(End))

Library yang digunakan hanya dua, yaitu tidyverse untuk memudahkan merapikan (tidying) data dan xlsx untuk menulis data ke Excel File.

Fungsi read_excel untuk (tentu saja, sesuai nama) membaca berkas spreadsheet, lalu %>% (pipa) bermakna “setelah itu, lakukan hal berikut …”, dalam hal ini, setelah baca excel file lalu lakukan mutate.

Mutate digunakan untuk mengubah tipe data pada kolom No, Start dan End menjadi integer dan date.

Coba Satu Nama

Berbelok sedikit dari penjelasan kode, penulis merasa perlu menjelaskan jembatan keledai yang ditempuh untuk sampai ke kode terakhir.

Kita perlu baby step untuk mencoba atau menemukan metode yang mangkus. Kita persempit data menjadi satu nama. Kode di bawah ini untuk mengambil perjalanan bisnis Ario Hendy.

ario_hendy <- df %>% 
  filter(ID_Person == 'Ario Hendy')

Uji Dua Kegiatan

keg_a <- ario_hendy %>% 
  filter(Event == 'Kegiatan A')

keg_c <- ario_hendy %>% 
  filter(Event == 'Kegiatan C')

keg_e <- ario_hendy %>% 
  filter(Event == 'Kegiatan E')

keg_g <- ario_hendy %>% 
  filter(Event == 'Kegiatan G')

(keg_a$Start <= keg_c$End) & (keg_a$End >= keg_c$Start)

(keg_c$Start <= keg_e$End) & (keg_c$End >= keg_e$Start)

(keg_e$Start <= keg_g$End) & (keg_e$End >= keg_g$Start)

Ambil beberapa kegiatan yang diikuti lalu masukkan pada formula.

Hasil formula di atas adalah FALSE atau TRUE. Kegiatan A dan Kegiatan C tidak bertabrakan (FALSE), Kegiatan C dan Kegiatan E ternyata bertumpuk, pada tanggal 3 Februari 2021 sehingga outputnya adalaah TRUE. Kegiatan E dan G tidak ada masalah (FALSE).

Proses membandingkan dua kegiatan itu perlu dilakukan untuk semua kegiatan. Karena Ario Hendy mengikuti 8 kegiatan, seharusnya untuk masing-masing kegiatan diuji 7 kali (8-1). Kegiatan A diuji dengan kegiatan selain A, yaitu kegiatan C, E, G, J, L, Q dan R.

Uji Semua Kegiatan Selain A

selain_a <- ario_hendy %>% 
  filter(Event != 'Kegiatan A')

if (nrow(selain_a) > 0) {
  for (row in 1:nrow(selain_a)) {
    rr <- (keg_a$Start <= selain_a[[row, 'End']]) & (keg_a$End >= selain_a[[row, 'Start']])
    print(rr)
  }
}

Kode di atas, mengambil semua data perjalanan Ario Hendy, selain Kegiatan A, terdapat 7 kegiatan. Pada masing-masing kegiatan tersebut, uji apakah ada yang bertabrakan dengan Kegiatan A. Ternyata semuanya FALSE, tidak ada yang bermasalah dengan Kegiatan A.

Uji Semua Kegiatan Selain C

selain_c <- ario_hendy %>% 
  filter(Event != 'Kegiatan C')

if (nrow(selain_c) > 0) {
  for (row in 1:nrow(selain_c)) {
    rr <- (keg_c$Start <= selain_c[[row, 'End']]) & (keg_c$End >= selain_c[[row, 'Start']])
    print(rr)
  }
}

Pada selain Kegiatan C terdapat satu yang bertabrakan. Agar hasil kode dapat lebih dipahami, kita dapat memunculkan di layar hanya jika terdapat hasil bernilai TRUE.

selain_c <- ario_hendy %>% 
  filter(Event != 'Kegiatan C')

if (nrow(selain_c) > 0) {
  for (row in 1:nrow(selain_c)) {
    rr <- (keg_c$Start <= selain_c[[row, 'End']]) & (keg_c$End >= selain_c[[row, 'Start']])
    if (rr) print(rr)
  }
}

Jika dirasa kurang informatif, kita dapat memunculkan juga tanggal dan nama kegiatan yang bertabrakan dengan Kegiatan C.

selain_c <- ario_hendy %>% 
  filter(Event != 'Kegiatan C')

if (nrow(selain_c) > 0) {
  for (row in 1:nrow(selain_c)) {
    rr <- (keg_c$Start <= selain_c[[row, 'End']]) & (keg_c$End >= selain_c[[row, 'Start']])
    if (rr) print(paste(
      keg_c$Start,
      keg_c$End,
      selain_c[[row, 'Start']],
      selain_c[[row, 'End']],
      selain_c[[row, 'Event']]
    ))
  }
}

apply

Kita tentu ingin melakukan pengujian untuk semua baris kegiatan tanpa harus menulis selain_a, selain_b, selain_c dan selain-selain lainnya. Untuk hal itu, R memiliki fitur apply yang berguna untuk melakukan sesuatu fungsi pada semua baris data.

Kita hanya perlu membuat fungsi yang membungkus kode yang akan dijalankan pada semua baris.

Sebagai ilustrasi, kita akan membuat kolom baru bernama duplikat_no, yang isinya adalah nomor baris ditambah kata “ini adalah”.

fungsi_ario <- function(x) {
  ret <- paste('ini adalah', x['No'], sep = ' ')
  return(ret)
}

ario_hendy$duplikat_no <- apply(ario_hendy, 1, fungsi_ario)

Fungsi paste pada R digunakan untuk menggabungkan teks.

Uji Semua Kegiatan Ario Hendy

Sekarang kita akan membuat sebuah fungsi yang menguji semua kegiatan Ario Hendy. Agar terbiasa dengan apply kita akan membuat sebuah fungsi sederhana yang akan memberikan angka 0 pada semua baris data.

f <- function(x) {
  ret <- 0
  return(ret)
}

ario_hendy$is_overlap <- apply(ario_hendy, 1, f)

Di saat ini semua kegiatan dianggap tidak bermasalah (angka 0 pada kolom is_overlap) karena kita belum membuat kode pengujian kegiatan pada fungsi f.

f <- function(x) {
  ret <- 0
  
  current_no <- as.integer(x['No'])
  current_start <- x['Start']
  current_end <- x['End']
  
  temp_df <- ario_hendy %>%
    filter(
      No != current_no
    )
  
  if (nrow(temp_df) > 0) {
    for (row in 1:nrow(temp_df)) {
      if (
        (current_start <= temp_df[[row, 'End']]) & 
        (current_end >= temp_df[[row, 'Start']])) {
        ret <- 1
      }
    }
  }
  
  return(ret)
}

ario_hendy$is_overlap <- apply(ario_hendy, 1, f)

Kode di atas telah melakukan pengujian pada semua data ario_hendy.

Variabel temp_df berisi semua data selain yang bernomor sama dengan baris yang sedang diuji. Karena kita menganggap satu baris data berisi kolom No yang unik.

Itulah alasan kenapa sangat disarankan sekali memiliki kolom Nomor Urut atau ID yang berisi nilai unik tiap transaksi, memudahkan pembuatan kode karena tidak perlu melakukan filter pada banyak kolom, cukup satu kolom.

Jika kita tidak memfilter kolom No selain yang sedang diuji maka kita akan selalu mendapati kolom is_overlap bernilai 1 karena suatu baris data akan dilagakan dengan baris data yang sama.

Aplikasikan pada Semua

Setelah berhasil meniti jembatan keledai, saatnya mengaplikasikan pada semua data. Kode di bawah ini kurang lebih sama dengan kode sebelumnya, beda hanya saat melakukan filter untuk membuat temp_df dan data yang digunakan pada fungsi apply.

f <- function(x) {
  ret <- 0
  
  current_no <- as.integer(x['No'])
  current_ID <- x['ID_Person']
  current_start <- x['Start']
  current_end <- x['End']
  
  temp_df <- df %>%
    filter(
      ID_Person == current_ID,
      No != current_no
    )
  
  if (nrow(temp_df) > 0) {
    for (row in 1:nrow(temp_df)) {
      if (
        (current_start <= temp_df[[row, 'End']]) & 
        (current_end >= temp_df[[row, 'Start']])) {
        ret <- 1
      }
    }
  }
  
  return(ret)
}

df$is_overlap <- apply(df, 1, f)

Hanya tampilkan kegiatan yang bertabrakan.

df_overlap <- df %>% 
  filter(is_overlap == 1) %>% 
  arrange(ID_Person, Start)

Dan akhirnya, tulis data ke dalam Excel file.

df_overlap %>% write.xlsx('df_overlap.xlsx')

Permasalahan Ditemui

80/20 Rule

Salah satu yang sering kita dengar adalah 80/20 rule, 80% waktu (data scientist) digunakan untuk mendapatkan, membersihkan dan mengatur data, menyisakan hanya 20% untuk melakukan analisis.

Celakanya, kita tidak imun dari hal itu. Sampai-sampai kita dapat mengadopsi dan memodifikasi pernyataan tersebut menjadi, “80% waktu (auditor) digunakan untuk meminta, menunggu, mendapatkan, membersihkan dan mengatur data, menyisakan cuma 20% untuk digunakan melakukan pengujian (audit test).”

Studi kasus ini menunjukkan kebenaran fenomena tersebut. Meski hanya membutuhkan dua kolom, tanggal mulai dan tanggal selesai, kita akan kepentok dengan masalah yang sama, data tidak bersih.

Berikut beberapa soalan yang kerap ditemui.

Isian bukan dalam bentuk tanggal

(Kolom data) tanggal adalah bintang utama dalam episod ini. Tanpanya the show must come to an end. Namun permintaan “diisi dengan tanggal” dapat dimaknai berbeda oleh orang lain. Mau(nya) kita tanggal ditulis sebagai:

17/03/2021

atau setidaknya:

03/17/2021

namun ditulis

17 Maret 2021

beserta pelbagai variasinya seperti “17 Mar 2021”, “17 Mar 21”, “Mar 17 21”. Lebih seru lagi jika melibatkan bahasa lain menjadi “17 March 2021”, “March, 27th 2021”. Keseruan berlipat jika saltik turut andil, “17 mr 2021”, “17 mare 2021”, “17 maret 2031”.

Salah satu solusi untuk hal seperti ini adalah dengan mengonversi teks menjadi tanggal dengan kode berikut.

df <- df %>% 
  mutate(
    daerah = 'luar',
    Start = as.character(Start),
    Start = case_when(
      grepl('17 maret 2031', Start) ~ as.Date('17/03/2021', '%d%m%Y', origin = '1899-12-30'),
      grepl('17 Mar 2021', Start) ~ as.Date('17/03/2021', '%d%m%Y', origin = '1899-12-30'),
      TRUE ~ as.Date(as.numeric(Start), origin = '1899-12-30')
    ))

Namun seperti terlihat, kita harus mengetik sekian banyak kemungkinan demi memperbaiki data. Tidak ideal, tapi masih lebih baik daripada tidak ada sama sekali.

Format tanggal tidak konsisten

Jika mendapati data seperti ini, kode di bawah ini akan menghasilkan Error.

df <- readxl::read_excel('folder//basangdata_date_overlap.xlsx') %>% 
  mutate(No = as.integer(No),
         Start = as.Date(Start),
         End = as.Date(End))

“Dosa” ini lebih ringan namun tetap saja membutuhkan upaya untuk menebusnya.

Solusinya adalah saat data load, ganti kolom berisi tanggal (Start dan End) menjadi character.

df <- readxl::read_excel('folder//basangdata_date_overlap.xlsx') %>% 
  mutate(No = as.integer(No),
         Start = as.character(Start),
         End = as.character(End))

Kemudian ubah kolom Start/End sesuai format masing-masing, jika format tanggal adalah Hari/Bulan/Tahun maka gunakan as.Date(kolom, ‘%d/%m%Y’, origin = ‘1899-12-30’).

Jika sedari tadi bingung kenapa origin yang digunakan adalah 30 Desember 1899 dapat merujuk ke Stackoverflow, R-Blogger dan Microsoft. Rangkumannya:

• 1899-12-30 untuk Excel di Windows; dan
• 1904-01-01 untuk Excel di Mac

df <- df %>% 
  mutate(
    Start = case_when(
      grepl('/', Start) ~ as.Date(Start, '%d/%m/%Y', origin = '1899-12-30'),
      TRUE ~ as.Date(as.numeric(Start), origin='1899-12-30')
    ),
    End = case_when(
      grepl('/', End) ~ as.Date(End, '%d/%m/%Y', origin = '1899-12-30'),
      TRUE ~ as.Date(as.numeric(End), origin='1899-12-30')
    )
  )

Tanggal Selesai Lebih Dulu

Apakah tertukar posisi antara tanggal selesai dan tanggal mulai? Apakah tanggal selesai semata salah ketik? Jangan-jangan bulan pada tanggal selesai salah ketik?

Ada banyak kemungkinan yang harus dibingungkan. Solusi paling manjur adalah Konfirmasi kepada penyedia data.

---

Simpulan

Pengujian kepatuhan pelaku perjalanan atas aturan/kebijakan pemberi kerja dapat diuji, dengan cepat, menggunakan sedikit kode R. Permasalahan utama justru pada keberadaan data bersih yang dapat diproses. Sumberdaya terbesar justru harus dialokasikan untuk merekam dan membersihkan data.

Jika pembaca memiliki ide atau metode lain yang lebih ekonomis, efektif dan efisien, silahkan tinggalkan komentar untuk didiskusikan.

---

Referensi

• https://www.ibm.com/cloud/blog/ibm-data-catalog-data-scientists-productivity
• https://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap
• http://ninjaname.net/indonesian_name.php
• https://www.r-bloggers.com/2013/08/date-formats-in-r/
• https://stackoverflow.com/questions/43230470/how-to-convert-excel-date-format-to-proper-date-in-r
• https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487

---

Cover Photo by Estée Janssens on Unsplash

---

Berlangganan

Suka tulisan di sini? Silahkan mengisi formulir di bawah ini untuk berlangganan agar kami dapat mengirim pemberitahuan tulisan baru.