Audit Findings Summary

Row

Total Audit Findings

17

New Audit Findings

29

Critical Audit Findings

4

Row

Historic Audit Findings

Audit Events

Row

Active Findings per Delivery Manager

Details for current Audit Findings

Compliance Score

Row

Jorge Castilleja

Ricardo Solares

Maria Isabel Calzada

Hector Arturo Ramirez Flores

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}
# load libraries ----
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(plotly)        # to create Interactive plots  
library(highcharter)   # to use highcharts for R
library(tidyverse)     # to handle data
library(hrbrthemes)    # to additional themes to the charts   
library(extrafont)     # to have additional fonts on charts
library(ggplot2)       # to create charts  
library(dygraphs)      # to create interactive slicers
library(htmlwidgets)   # to show widgets in R markdown
library(ggrepel)
library(quantmod)      # to create a timeseries with xts objects in high charts
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("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 
    `Critical`,    
    `Non-Critical`,
    na.rm = TRUE)) %>% 
  arrange(desc(`Grand Total`))    # Sort bars in descending order(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 > 0, "warning", "primary"))
```

Row {data-width=600 .tabset .tabset-fade}
-------------------------------------

### Historic Audit Findings
```{r}
# load data
dataf <- read_excel("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(20230227) %>% datetime_to_timestamp(),
        color = "#FF0000",
        label = list(
          text = "1st Internal Audit 2023",
          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_x_date(date_breaks = "3 months", date_labels = "%b %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("2023-02-27"), 
             y = 08, label="1st Internal \nAudit 2023") +
  annotate(geom="point", x=as.Date("2023-02-27"), 
           y = 2, size = 5, shape = 21, 
           fill = "transparent") +
  annotate(geom="text", x=as.Date("2022-08-29"), 
             y = 22, label="2nd Internal \nAudit 2022") +
  annotate(geom="point", x=as.Date("2022-08-29"), 
           y = 16, 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`,
                               marginRight = 150,spacingLeft = 100
                               ), 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 = 14) %>%
        hc_xAxis(title = list(text = ""))
    }) %>%
  hw_grid(ncol = 3, 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 {data-width=600 .tabset .tabset-fade}
-------------------------------------
### Jorge Castilleja
```{r}
# Set the proper color pallete according elementary Theme
break_down_02 <- read.csv("compliance total.csv")

# parse dates
break_down_02$Month <- mdy(break_down_02$Month)
break_down_02$Project_name <- as.factor(break_down_02$Project_name)

# filter by active projects
active_projects_02 <- break_down_02 %>%
  filter(Status == "Active")

# create DM dataframe
jcastilleja <- active_projects_02 %>%
  filter(Delivery_manager == "Jorge Castilleja Villafana") %>%
  group_by(`Project_name`) %>%
  group_map(
    ~{
      .x %>%
        hchart("line", hcaes(x = `Month`,y = `Comp.`*1000),
               marginRight = 500,
               spacingLeft = 300
               ) %>%
        hc_title(text = .y$`Project_name`) %>%
        hc_exporting(enabled = TRUE)  %>%
        hc_yAxis(min = 0, max = 100) %>%
        hc_yAxis(labels = list(format = "{value}%")) %>%
        hc_xAxis(title = list(text = "")) %>%
        hc_xAxis(title = list(text = "")) %>%
        hc_legend(align = "right", verticalAlign = "middle"
            ) %>%
  hc_yAxis(title = list(text = "Compliance Score"),
           plotLines = list(
             list(value = 80, color = "red", width = 2,dashStyle = "shortdash",
                  label = list(
                    text = "Target")
             )
           )
  )
    }) %>%
  hw_grid(ncol = 2, rowheight = "270px")
jcastilleja
```
### Ricardo Solares
```{r}
rsolares <- active_projects_02 %>%
  filter(Delivery_manager == "Ricardo Solares Gonzalez") %>%
  group_by(`Project_name`) %>%
  group_map(
    ~{
      .x %>%
        hchart("line", hcaes(x = `Month`,y = `Comp.`*1000),
               marginRight = 500,
               spacingLeft = 300
               ) %>%
        hc_title(text = .y$`Project_name`) %>%
        hc_exporting(enabled = TRUE)  %>%
        hc_yAxis(min = 0, max = 100) %>%
        hc_yAxis(labels = list(format = "{value}%")) %>%
        hc_xAxis(title = list(text = "")) %>%
        hc_xAxis(title = list(text = "")) %>%
        hc_legend(align = "right", verticalAlign = "middle"
            ) %>%
  hc_yAxis(title = list(text = "Compliance Score"),
           plotLines = list(
             list(value = 80, color = "red", width = 2,dashStyle = "shortdash",
                  label = list(
                    text = "Target")
             )
           )
  )
    }) %>%
  hw_grid(ncol = 2, rowheight = "270px")
rsolares
```
### Maria Isabel Calzada
```{r}
mcalzada <- active_projects_02 %>%
  filter(Delivery_manager == "Fernando David Torres Hernandez") %>%
  group_by(`Project_name`) %>%
  group_map(
    ~{
      .x %>%
        hchart("line", hcaes(x = `Month`,y = `Comp.`*1000),
               marginRight = 500,
               spacingLeft = 300
               ) %>%
        hc_title(text = .y$`Project_name`) %>%
        hc_exporting(enabled = TRUE)  %>%
        hc_yAxis(min = 0, max = 100) %>%
        hc_yAxis(labels = list(format = "{value}%")) %>%
        hc_xAxis(title = list(text = "")) %>%
        hc_xAxis(title = list(text = "")) %>%
        hc_legend(align = "right", verticalAlign = "middle"
            ) %>%
  hc_yAxis(title = list(text = "Compliance Score"),
           plotLines = list(
             list(value = 80, color = "red", width = 2,dashStyle = "shortdash",
                  label = list(
                    text = "Target")
             )
           )
  )
    }) %>%
  hw_grid(ncol = 1, rowheight = "270px")
mcalzada
```
### Hector Arturo Ramirez Flores
```{r}
hector_ramirez <- active_projects_02 %>%
  filter(Delivery_manager == "Hector Arturo Ramirez Flores ") %>%
  group_by(`Project_name`) %>%
  group_map(
    ~{
      .x %>%
        hchart("line", hcaes(x = `Month`,y = `Comp.`*1000),
               marginRight = 500,
               spacingLeft = 300
               ) %>%
        hc_title(text = .y$`Project_name`) %>%
        hc_exporting(enabled = TRUE)  %>%
        hc_yAxis(min = 0, max = 100) %>%
        hc_yAxis(labels = list(format = "{value}%")) %>%
        hc_xAxis(title = list(text = "")) %>%
        hc_xAxis(title = list(text = "")) %>%
        hc_legend(align = "right", verticalAlign = "middle"
            ) %>%
  hc_yAxis(title = list(text = "Compliance Score"),
           plotLines = list(
             list(value = 80, color = "red", width = 2,dashStyle = "shortdash",
                  label = list(
                    text = "Target")
             )
           )
  )
    }) %>%
  hw_grid(ncol = 1, rowheight = "350px")
hector_ramirez
```
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`,
    `First of 2023`,
    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:3)                            # 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) 
```