Audit Findings Summary

Row

Total Audit Findings

19

New Audit Findings

29

Critical Audit Findings

13

Row

Historic Audit Findings

Audit Events

Row

Active Findings per Delivery Manager

Details for current Audit Findings

Compliance Score

Row

Jorge Castilleja

Ricardo Solares

Fernando Torres

Row

Historical Area Plot

Historical Stacked Area Plot

Cause Analysis

Row

Most common findings per DM

When Active Audit Findings were created ?

Row

Main causes during 2nd Internal Audit 2022

Main causes of Findings YTD

---
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
```{r}
Audit_Findings <- Pivot_table_summary$`Grand Total`

valueBox(Audit_Findings, caption = "Total Audit Findings", icon="fa-thumbs-down")
```

### New Audit Findings
```{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
```{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
```{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
```{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
```{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
```{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
```{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
```{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
```{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

```{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

```{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

```{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 ?

```{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

```{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("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
    
```{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) 
```