Before beginning the following exercise, we import this chapter's exercise files into the QDF as we did in Chapter 2, Sales Perspective. Let's create a bar chart that analyzes OTIF with the following property options in 1.ApplicationOperations_Perspective_Sandbox.qvw:

sum({$<[Process Type]={'Sales'},[Delivery First Step]={'No'}>} if([Delivery Document Date]<=[Order Due Date] and [Order Quantity]=rangesum([Delivery Quantity],-[Return Quantity]),1))/count({$<[Process Type]={'Sales'},[Delivery First Step]={'No'}>} DISTINCT [Delivery Line No.]
) 

sum({$<[Process Type]={'Sales'},[Delivery First Step]={'No'}>} if([Delivery Document Date]<=[Order Due Date] and [Order Quantity]=rangesum([Delivery Quantity],-[Return Quantity]),[Delivery Quantity]))/sum({$<[Process Type]={'Sales'},[Delivery First Step]={'No'}>} [Delivery Quantity])

We use the Delivery Year-Month field as a dimension because we specifically want to analyze the deliveries in the month that they were made. In each expression, we use an if-statement within the sum() function. For better performance, we can also migrate this logic to the script and create a field in the data model that identifies which line items were OTIF. The conditional expression of the if-statement evaluates whether what we promised in the sales order matches the delivery. Therefore, we filter out delivery documents that start a sales cycle in the set analysis because no promise was ever documented in the ERP system.

An accumulating snapshot tends to contain many null values that represent steps in the cycle that have yet to happen or that never will. In QlikView, binary functions, such as +, -, *, and /, do not work when one of the variables is a null value. For example, the =8+null() expression returns a null value instead of eight. On the other hand, the rangesum() function treats a null value as if it were zero, so we add we use it to sum [Delivery Quantity] and [Return Quantity] because they often contain a null value.

We can gather from our OTIF analysis that, for six months in 2013, we delivered 100% of our orders on-time and in-full. The most difficult month in 2013 was June when we delivered 80% of the line items and 72% of the quantity satisfactorily. Assuming that we use a standard unit of measurement for all of our products, this discrepancy between line items and quantity is due to a higher OTIF among lower-quantity deliveries. Let's now analyze how we work through our data to discover opportunities to improve our OTIF.

There are various ways to analyze OTIF in more detail. Just like we did with the DSI calculation in our working capital perspective, we can break up the OTIF calculation into its parts. For example, we can analyze on-time deliveries separately from in full deliveries and also analyze the total absolute number of deliveries per month see its behavior.

We can also analyze the different cycles that support OTIF. For example, we can explore data from the production cycle, the purchasing cycle, or the shipping cycle. As we've added the purchasing cycle to our operations perspective, we can easily analyze our suppliers' OTIF rate in the following chart:

Exercise 6.2

To perform this exercise:

1. Clone the chart that we created in Exercise 6.1.
2. Change the code, [Process Type]={'Sales'}, in the metric expressions to [Process Type]={'Purchasing'}.

Finally, we can break down the cycles by their corresponding dimensions, such as supplier, customer, sales person, or item. We notice in the previous chart that our suppliers do not have a high OTIF and we decide to break down the metric by supplier:

To perform this exercise:

We've created the [Delivery Days Late] field in the script to make for cleaner code and reduce calculation time. For larger datasets, it may also be necessary to create days late bins in the script instead of using a calculated dimension, as we did in this exercise. Either way, we should create bins using the dual() function so that we can easily sort them.

We can use this chart for any step in a cycle that has a due date. We group all on-time instances in one bin and then we divide late instances into bins that represent ranges that are analytically significant. On the other hand, if we simply want to analyze the time that it takes to complete a step, we can use a histogram. Let's take what we've discovered about our suppliers' OTIF and analyze each item's real lead time.

Lead time is the time that it takes from the moment that we order an item to the moment that we receive it in inventory. We first saw lead time in our working capital perspective in Chapter 5, Working Capital Perspective. In that chapter, we used a predefined lead time to calculate each item's reorder stock level. In this section we look at how to use data to calculate a more accurate lead time. We can also apply these same methods to analyze the time taken to complete any of the steps in a cycle. For example, we can measure how long it takes to generate a customer invoice or convert a quotation into an order.

We begin our analysis by visualizing the average time that it takes from the moment we create a purchase order until we create an inventory delivery receipt. We analyze the trend of average lead times by month.

Exercise 6.4

Let's create a bar chart with the following dimensions and metrics options:

Dimensions
Label	Value
OrderYear-Month	OrderYear-Month
Expressions
Label	Value
Average Lead Time	avg({$<[Process Type]={'Purchasing'}>} [Lead Time])

We create a [Lead Time] field in the script that is the difference between [Delivery Document Date] and [Order Document Date]. The avg() function works without the help of aggr() because we want the average lead time of each line item, which has the same level of detail as our accumulating snapshot.

If the only statistical measurement that we use is average, then we risk over-simplifying our analysis and failing to define optimal inventory levels. Let's study the time span between a purchase order and its receipt in more detail with a distribution analysis that we first saw in the sales perspective in Chapter 2, Sales Perspective:

When we want to test whether two numeric metrics are correlated, we use scatterplot charts and R-squared. Similarly, we can also test the correlation between two categorical groups using the Chi-squared test of independence.

In this example, we want to confirm that the supplier is not one of the factors that determines a delivery's timeliness. In order to test this hypothesis, we calculate a value called p, which is the probability that supplier and delivery status are independent. Before analyzing the results of the Chi-squared test of independence, we decide that if p is less than .05, then we will reject the assumption that delivery timeliness does not depend on the supplier. This then implies that there is a relationship between them. We call this point (.05) where we would reject the hypothesis of independence as the critical point. Let's analyze and evaluate whether there is a relationship between these two variables: