Fill Rate

Fill rate is surprisingly easy to calculate from the PO Items table. This script is implimented in the Fill Rate dataflow in Domo.

library(DomoR)
library(tidyverse)
library(lubridate)

load("domoCustomer")
load("domoAccessToken")
init(domoCustomer, domoAccessToken)

poItems <- fetch("b8d4c659-5735-41b7-adb7-b6a42be1aed7")

fillRate <- poItems %>%
        filter(exclude_from_fill_rate == 0,
               dropship_po == 0) %>%
        mutate(qty_ordered = ifelse(qty_ordered == 0 & original_qty_ordered > 0,
                                    original_qty_ordered,
                                    qty_ordered),
               date_received = as_date(date_received),
               date_received = ifelse(is.na(date_received),
                                      ymd("2099-12-31"),
                                      as_date(date_received)),
               date_received = as_date(date_received)) %>%
        group_by(purchase_orders_id) %>%
        nest() %>%
        mutate(on_time = map(data, (function(df) df$date_received <= as_date(min(df$date_received)) + 1)),
               no_items_received = map(data, (function(df)df$date_received == ymd("2099-12-31") &
                                                      as_date(min(df$date_received)) == ymd("2099-12-31")))) %>%
        unnest() %>%
        mutate(qty_received_on_time = ifelse(on_time == T, qty_received, 0),
               month = month(date_received),
               year = year(date_received),
               week = week(date_received),
               on_time = as.character(on_time),
               no_items_received = as.character(no_items_received))