18
29
12
---
title: "Process Compliance Dashboard"
output:
flexdashboard::flex_dashboard:
orientation: rows
source_code: embed
---
```{r setup, include=FALSE}
library(flexdashboard)
library(readxl) # to read normal xls files
library(dplyr) # to handle data
library(stringr) # to handle strings
library(forecast) # to create a timeseries object
library(lubridate) # to handle date formats
library(ggthemes) # to additional themes to the charts
library(hrbrthemes) # to additional themes to the charts
library(tidyquant) # to additional themes to the charts
library(extrafont) # to have additional fonts on charts
library(ggplot2) # to create charts
library(plotly) # to create Interactive plots
library(dygraphs) # to create interactive slicers
library(htmlwidgets) # to show widgets in R markdown
library(ggrepel)
library(highcharter) # to use highcharts for R
library(quantmod) # to create a timeseries with xts objects in high charts
library(tidyverse) # to handle data
library(DT) # to creat Jquery tables
library(treemap) # to create tree maps
library(treemapify) # to create custom treemaps
library(viridis) # to use several pallete colors
library(data.table)
# Load main data
Pivot_raw <- read_excel("C:\\Users\\ar.santana\\Documents\\Audits\\Audit Findings\\Findings Report Source 2022.xlsx", sheet = "Current Findings_Pivot", col_names = TRUE)
Pivot_raw$`1` <- as.Date(Pivot_raw$`1`, origin = "1899-12-30")
round(Pivot_raw$`Days to close`)
# Filter only active findings
Pivot_raw_2 <- Pivot_raw %>%
filter(`Request Status:`!= "Done" & `Request Status:` != "Canceled")
# to create a pivot table with active findings
Pivot_table_summary <- Pivot_raw_2 %>% # dataframe to use
select(`Priority`) %>% # Select only relevant columns from data
group_by(`Priority`) %>% # Group data
summarise(n = n()) %>% # Create a column n for count
spread(`Priority`, n) %>%
rowwise() %>%
mutate(`Grand Total` = sum( # Grand total column must be used to order bar in the next pipe
`Critical`,
`Non-Critical`,
na.rm = TRUE)) %>%
arrange(desc(`Grand Total`)) # Sort bars in descending order according Grand Total Column
# to create a New Audit Findings pivot
new <- Pivot_raw %>% # dataframe to use
select(`Audit`) %>% # Select only relevant columns from data
group_by(`Audit`) %>% # Group data
summarise(n = n()) %>% # Create a column n for count
spread(`Audit`, n) %>%
rowwise()
pt_dm_priority <- Pivot_raw_2 %>% # dataframe to use
select(`Delivery manager`,`WBS:`,`Priority`) %>% # Select only relevant columns from data
group_by(`Delivery manager`,`WBS:`,`Priority`) %>% # Group data
summarise(n = n()) %>% # Create a column n for count
spread(`Priority`, n) %>%
rowwise() %>%
mutate(`Grand Total` = sum(`Critical`, `Non-Critical`, na.rm = TRUE)) %>%
arrange(desc(`Grand Total`))
# convert as long format using gather function
long_dm_priority <- gather(pt_dm_priority, # Name of the data frame
key = "Priority", # Name of the key column to create
value = "Total", # Name of the value column to create
3:4) # Specify which columns to gather from
```
Audit Findings Summary
=====================================
Row
-------------------------------------
### Total Audit Findings {.mobile}
```{r}
Audit_Findings <- Pivot_table_summary$`Grand Total`
valueBox(Audit_Findings, caption = "Total Audit Findings", icon="fa-thumbs-down")
```
### New Audit Findings {.mobile}
```{r}
new_findings <- new$`Second of 2022`
valueBox(new_findings, caption = "Findings during last Audit",
icon='fa-sign-out-alt'
#icon="fa-comments"
)
```
### Critical Audit Findings {.mobile}
```{r}
critical <- Pivot_table_summary$Critical
valueBox(critical,
icon = "fa-trash",
color = ifelse(critical > 10, "warning", "primary"))
```
Row {data-width=600 .tabset .tabset-fade}
-------------------------------------
### Historic Audit Findings {.mobile}
```{r}
# load data
dataf <- read_excel("C:\\Users\\ar.santana\\Documents\\Audits\\Audit Findings\\Findings Report Source 2022.xlsx",
sheet = "Match findings", col_names = TRUE)
# Rename columns
colnames(dataf) <- c("Week", "Findings")
# transform as date the week column
dataf$Week <- ymd(dataf$Week)
# To create xts object to be able to use dygraph or quantmood visualizations
don <- xts(x = dataf$Findings, order.by = dataf$Week)
# Define the pallete object color for area plot
cols <- c("#69b3a2")
# To create the timeseries interactive plot
hc <- hchart(don,
type = "area",
name = "Total Audit Findings", # Set the over scroll description label
color = cols,
fillOpacity = 0.2) %>%
hc_add_theme(hc_theme_elementary()) %>% # Define the proper theme
hc_xAxis(
plotLines = list(
list(
value = ymd(20220225) %>% datetime_to_timestamp(),
color = "#FF0000",
label = list(
text = "1st Internal Audit 2022",
align = "top",
style = list(color = "#FF0000")
)
)
)
) %>%
hc_xAxis(
plotBands = list(
list(
from = ymd(20220826) %>% datetime_to_timestamp(),
to = ymd(20221007) %>% datetime_to_timestamp(),
color = "rgba(85, 37, 131, 0.2)", # RGB specification of the lakers purple color with a 30% alpha
label = list(
text = "2nd Internal \nAudit 2022", # Text for the plotBand
style = list(fontWeight = "bold", color = "#0F0F0F", fontSize = "12px")
)
)
)
) %>%
hc_yAxis(min = 0, max = 85) %>%
hc_exporting(enabled = TRUE)
hc
```
### Audit Events {.mobile}
```{r}
# To make a time series plot
pty <- ggplot(dataf, aes(x = Week, y = `Findings`, group = 1)) +
geom_line(color="#69b3a2") +
geom_area(fill="#69b3a2", alpha=0.2) +
scale_x_date(date_labels = "%Y") +
scale_y_continuous(limits = c(0, 80)) +
labs(x = "Year",
y = "Audit Findings") +
# Highlight Audit events in the chart
annotate(geom="text", x=as.Date("2022-08-29"),
y = 25, label="2nd Internal \nAudit 2022") +
annotate(geom="point", x=as.Date("2022-08-29"),
y = 17, size = 5, shape = 21,
fill = "transparent") +
annotate(geom="text", x=as.Date("2022-02-04"),
y = 14, label="1st Internal \nAudit 2022") +
annotate(geom="point", x=as.Date("2022-02-25"),
y = 9, size = 5, shape = 21,
fill = "transparent") +
annotate(geom="text", x=as.Date("2021-09-10"),
y = 33, label="2nd Internal \nAudit 2021") +
annotate(geom="point", x=as.Date("2021-09-10"),
y = 25, size = 5, shape = 21,
fill = "transparent") +
annotate(geom="text", x=as.Date("2021-03-05"),
y = 12, label="1st Internal \nAudit 2021") +
annotate(geom="point", x=as.Date("2021-03-05"),
y = 7, size = 5, shape = 21,
fill = "transparent") +
annotate(geom="text", x=as.Date("2020-09-25"),
y = 10, label="2nd Internal \nAudit 2020") +
annotate(geom="point", x=as.Date("2020-09-25"),
y = 7, size = 5, shape = 21,
fill = "transparent") +
annotate(geom="text", x=as.Date("2020-04-10"),
y = 10, label="1st Internal \nAudit 2020") +
annotate(geom="point", x=as.Date("2020-04-10"),
y = 7, size = 5, shape = 21,
fill = "transparent") +
annotate(geom="text", x=as.Date("2020-01-17"),
y = 75, label="Split of 4863 and \n5567 Projects") +
annotate(geom="point", x=as.Date("2020-01-17"),
y = 72, size = 5, shape = 21,
fill = "transparent") +
annotate(geom="text", x=as.Date("2021-10-08"),
y = 17, label="PDP implementation to \nSpecialized Services") +
annotate(geom="point", x=as.Date("2021-10-08"),
y = 10, size = 5, shape = 21,
fill = "transparent") +
theme_minimal() +
theme(axis.title = element_text(),
axis.title.x = element_blank(),
axis.ticks.x = element_blank(),
plot.margin = margin(t = 0, r = 0,b = 0,l = 0)
)
# Create an Interactive visualization
ggplotly(pty)
```
Row {data-width=600 .tabset .tabset-fade}
-------------------------------------
### Active Findings per Delivery Manager {.mobile}
```{r}
# Set the proper color pallete according elementary Theme
colors_findings <- c("#FF9933","#66B2FF")
# plot data
long_dm_priority %>%
group_by(`Delivery manager`) %>%
group_map(
~{
.x %>%
hchart("column", hcaes(x = `WBS:`,y = Total, group = `Priority`), stacking = "normal") %>%
hc_title(text = .y$`Delivery manager`) %>%
hc_colors(colors_findings) %>%
hc_exporting(enabled = TRUE) %>%
hc_chart(inverted = TRUE) %>%
hc_yAxis(min = 0, max = 6) %>%
hc_xAxis(title = list(text = ""))
}) %>%
hw_grid(ncol = 4, rowheight = "350px")
```
### Details for current Audit Findings {.mobile}
```{r}
details <- Pivot_raw_2 %>%
select(`WBS:`, `Control (or Process Area):`, `Request Status:`, Location,Priority,Category, Audit, `Delivery manager`)
datatable(details,
options = list(pageLength = 10,
autoWidth = FALSE,
rownames = FALSE),
class = 'cell-border stripe'
)
```
Compliance Score {data-orientation=rows}
=====================================
Row
-------------------------------------
### Jorge Castilleja {.mobile}
```{r}
# load data
break_down <- read.csv("C:\\Users\\ar.santana\\Documents\\Data Science\\compliance total_oct_2022.csv")
# parse dates
break_down$Month <- mdy(break_down$Month)
break_down$Project_name <- as.factor(break_down$Project_name)
active_projects <- break_down %>%
filter(Status == "Active")
# set pallete color
colors_jorge <- viridis::turbo(n = 6)
# create highchart
options(highcharter.theme = hc_theme_elementary(tooltip = list(valueDecimals = 2)))
jorge <- active_projects %>%
filter(Delivery.Manager == "Jorge Castilleja Villafaña") %>%
hchart('line', hcaes(x = `Month`, y = `Comp.`*1000, group = `Project_name`)
) %>%
hc_xAxis(title = list(text = "")) %>%
hc_yAxis(title = list(text = "Compliance Score"),
plotLines = list(
list(value = 80, color = "red", width = 2,dashStyle = "shortdash",
label = list(
text = "Target")
)
)
) %>%
hc_yAxis(labels = list(format = "{value}%")) %>%
hc_yAxis(min = 30, max = 110) %>%
hc_colors(colors_jorge) %>%
hc_legend(align = "right", verticalAlign = "middle",
layout = "vertical") %>%
hc_navigator(enabled = TRUE) %>%
# hc_rangeSelector(enabled = TRUE) %>%
hc_exporting(enabled = TRUE)
jorge
```
### Ricardo Solares {.mobile}
```{r}
colors_ricardo <- viridis::turbo(n = 8)
options(highcharter.theme = hc_theme_elementary(tooltip = list(valueDecimals = 2)))
solares <- active_projects %>%
filter(Delivery.Manager == "Ricardo Solares Gonzalez") %>%
hchart('line', hcaes(x = `Month`, y = `Comp.`*1000, group = `Project_name`)
) %>%
hc_xAxis(title = list(text = "")) %>%
hc_yAxis(title = list(text = "Compliance Score"),
plotLines = list(
list(value = 80, color = "red", width = 2,dashStyle = "shortdash",
label = list(
text = "Target")
)
)
) %>%
hc_yAxis(labels = list(format = "{value}%")) %>%
hc_yAxis(min = 30, max = 110) %>%
hc_legend(align = "right", verticalAlign = "middle",
layout = "vertical") %>%
hc_colors(colors_ricardo) %>%
hc_navigator(enabled = TRUE) %>%
# hc_rangeSelector(enabled = TRUE) %>%
hc_exporting(enabled = TRUE)
solares
```
### Fernando Torres{.mobile}
```{r}
# create highchart
options(highcharter.theme = hc_theme_google(tooltip = list(valueDecimals = 2)))
fernando <- active_projects %>%
filter(Delivery.Manager == "Fernando David Torres Hernandez") %>%
hchart('line', hcaes(x = `Month`, y = `Comp.`*1000, group = `Project_name`)
) %>%
hc_xAxis(title = list(text = "")) %>%
hc_yAxis(title = list(text = "Compliance Score"),
plotLines = list(
list(value = 80, color = "red", width = 2,dashStyle = "shortdash",
label = list(
text = "Target")
)
)
) %>%
hc_yAxis(labels = list(format = "{value}%")) %>%
hc_yAxis(min = 30, max = 110) %>%
hc_legend(align = "right", verticalAlign = "middle",
layout = "vertical") %>%
hc_navigator(enabled = TRUE) %>%
# hc_rangeSelector(enabled = TRUE) %>%
hc_exporting(enabled = TRUE)
fernando
```
Row {data-width=600 .tabset .tabset-fade}
-------------------------------------
### Historical Area Plot {.mobile}
```{r}
# load data:
df <- read.csv("C:\\Users\\ar.santana\\Documents\\Data Science\\test area map_02.csv")
# rename columns
colnames(df) <- c("week","Core Support","Asset Management","CMDB","Comerce and Identity","GS","Sports","ONS","EA IT","LSR Automation","EA Observability", "GS Testing", "RSG Specialized Services")
# transform week column to date format
df$week <- mdy(df$week)
# set pallete color
custom_colors <- viridis::turbo(n = 12)
# reshape dataframe using gather
df_long <- gather(df, # Name of the data frame
key = "Project", # Name of the key column to create
value = "performance",# Name of the value column to create
2:13) # Specify which columns to gather from
# transform project columns as factor
df_long$Project <- as.factor(df_long$Project)
# Set highcharter options for only 2 decimals
options(highcharter.theme = hc_theme_elementary(tooltip = list(valueDecimals = 0)))
hc_test <- df_long %>%
hchart(type = "area",
hcaes(x = week, y = performance,
group = `Project`,
fillOpacity = 0.2)) %>%
hc_xAxis(title = list(text = "")) %>%
hc_colors(custom_colors) %>%
hc_plotOptions(series = list(stacking = 'normal')) %>%
hc_navigator(enabled = TRUE) %>%
hc_rangeSelector(enabled = TRUE) %>%
hc_exporting(enabled = TRUE) %>%
hc_legend(align = "right",
verticalAlign = "middle",
layout = "vertical")
hc_test
```
### Historical Stacked Area Plot {.mobile}
```{r}
# load data:
df_stacked <- read.csv("C:\\Users\\ar.santana\\Documents\\Audits\\Audit Findings\\df_melt_area.csv")
#
df_stacked$week <- mdy(df_stacked$week)
# Set highcharter options for only 2 decimals
options(highcharter.theme = hc_theme_elementary(tooltip = list(valueDecimals = 2)))
hc_test_stacked <- df_stacked %>%
hchart(type = "area",
hcaes(x = week, y = Perc*100,
group = variable,
fillOpacity = 0.2)) %>%
hc_xAxis(title = list(text = "")) %>%
hc_yAxis(title = list(text = "Percentage per Project")) %>%
hc_yAxis(labels = list(format = "{value}%")) %>%
hc_yAxis(min = 0, max = 100) %>%
hc_colors(custom_colors) %>%
hc_plotOptions(series = list(stacking = 'normal')) %>%
hc_exporting(enabled = TRUE) %>%
hc_legend(align = "right",
verticalAlign = "middle",
layout = "vertical")
hc_test_stacked
```
Cause Analysis
=====================================
Row
-------------------------------------
### Most common findings per DM {.mobile}
```{r}
custom_colors_01 <- viridis::plasma(n = 10)
options(highcharter.theme = hc_theme_elementary(tooltip = list(valueDecimals = 2)))
Pivot_raw %>%
count(`Delivery manager`, Category) %>%
group_by(`Delivery manager`) %>%
mutate(prop = ((n / sum(n))) * 100) %>%
hchart('column', hcaes(x = `Delivery manager`, y = `prop`,group = `Category`)) %>%
hc_chart(inverted = TRUE) %>%
hc_plotOptions(column = list(dataLabels = list(enabled = FALSE), stacking = "normal",
enableMouseTracking = TRUE)) %>%
hc_colors(custom_colors_01) %>%
hc_yAxis(title='Findings') %>%
hc_yAxis(labels = list(format = "{value}%")) %>%
hc_yAxis(min = 0, max = 100) %>%
hc_legend(align = "right", verticalAlign = "middle", layout = "vertical") %>%
hc_exporting(enabled = TRUE)
```
### When Active Audit Findings were created ? {.mobile}
```{r}
custom_colors_02 <- viridis::plasma(n = 3)
# to create a traditional pivot table
Pivot_table_audit <- Pivot_raw_2 %>% # dataframe to use
select(`WBS:`,`Audit`) %>% # Select only relevant columns from data
group_by(`WBS:`,`Audit`) %>% # Group data
summarise(n = n()) %>% # Create a column n for count
spread(`Audit`, n) %>%
rowwise() %>%
mutate(`Grand Total` = sum( # Grand total column must be used to order bar in the next pipe
# `First of 2021`,
`Second of 2021`,
`First of 2022`,
`Second of 2022`,
na.rm = TRUE)) %>%
arrange(desc(`Grand Total`)) # Sort bars in descending order according Grand Total Column
# convert as long format using gather function
long_format_data_audit <- gather(Pivot_table_audit, # Name of the data frame
key = "Audit_", # Name of the key column to create
value = "Total", # Name of the value column to create
2:4) # Specify which columns to gather from
# Set the main theme and decimal positions
options(highcharter.theme = hc_theme_elementary(tooltip = list(valueDecimals = 0)))
# to create stacked barchart
long_format_data_audit %>%
hchart('column', hcaes(x = `WBS:`, y = `Total`,group = `Audit_`
)) %>%
hc_chart(inverted = TRUE) %>%
hc_plotOptions(column = list(dataLabels = list(enabled = FALSE), stacking = "normal",
enableMouseTracking = TRUE)) %>%
hc_yAxis(title='Findings') %>%
hc_colors(custom_colors_02) %>%
hc_exporting(enabled = TRUE)
```
Row {.tabset .tabset-fade}
-------------------------------------
### Main causes during 2nd Internal Audit 2022 {.mobile}
```{r}
pt_dm_category <- Pivot_raw_2 %>% # dataframe to use
select(`Delivery manager`,`WBS:`,`Category`) %>% # Select only relevant columns from data
group_by(`Delivery manager`,`WBS:`,`Category`) %>% # Group data
summarise(n = n()) %>% # Create a column n for count
spread(`Category`, n) %>%
rowwise() %>%
mutate(`Grand Total` = sum(`Business Review`, `DRP`, `Onboarding/Offboarding`, `PPM Records` , na.rm = TRUE)) %>%
arrange(desc(`Grand Total`))
# convert as long format using gather function
long_dm_category <- gather(pt_dm_category, # Name of the data frame
key = "Category", # Name of the key column to create
value = "Total", # Name of the value column to create
3:6) # Specify which columns to gather from
# plot data
long_dm_category %>%
group_by(`Delivery manager`) %>%
group_map(
~{
.x %>%
hchart("pie", hcaes(x = `Category`,y = `Total`
#, group = `Category`
)
#, stacking = "normal"
,
innerSize = '75%') %>%
hc_title(text = .y$`Delivery manager`) %>%
#hc_colors(colors_findings) %>%
hc_exporting(enabled = TRUE) %>%
#hc_chart(inverted = TRUE) %>%
# hc_yAxis(min = 0, max = 6) %>%
hc_xAxis(title = list(text = ""))
}) %>%
hw_grid(ncol = 4, rowheight = "450px")
```
### Main causes during 2nd Internal Audit 2022 {.mobile}
```{r}
# 1) Wrangle your data into the necessary format
tree_2nd_Audit <- Pivot_raw %>%
filter(Audit == "Second of 2022") %>%
count(Category) %>%
arrange(n)
# 2) Create a basic top-level graph
tree_2nd_Audit %>%
hchart(type = "pie") %>%
hc_xAxis(categories = tree_2nd_Audit$Category) %>%
hc_add_series(tree_2nd_Audit)
#"treemap",
# hcaes(x = Category, value = n, color = n)) %>%
#hc_colorAxis(stops = color_stops(colors = viridis::inferno(15))) %>%
#hc_legend(align = "right", verticalAlign = "top",layout = "vertical") %>%
#hc_exporting(enabled = TRUE) #%>%
```
### Main causes of Findings YTD {.mobile}
```{r}
# 1) Wrangle your data into the necessary format
tree_ytd <- Pivot_raw %>%
filter(Audit != "First of 2021" & Audit != "Second of 2021" ) %>%
count(Category) %>%
arrange(n)
# 2) Create a basic top-level graph
tree_ytd %>%
hchart("treemap", hcaes(x = Category, value = n, color = n)) %>%
hc_colorAxis(stops = color_stops(colors = viridis::inferno(15))) %>%
hc_legend(align = "right", verticalAlign = "top",layout = "vertical") %>%
hc_exporting(enabled = TRUE)
```