One of the actions you can do with Analytics is to identify items that are out of stock in a retail location but in stock in the Warehouse or in a second store. There are ways that you can use Analytics to make this type of insight easier to find, depending on what your search criteria are.

Step one: starting with an inventory report

To start your report, begin with an Items report. 

1. To prepare it, add the Dimension of the Item Description (or identifier you are using, System ID, Custom SKU, UPC, and so on). 

   

2. Pivot on the Multi-Shop Store location.

   

3. Filter the report just to the stores that you're looking at. (In this example, there are two stores, but builds like this can be of evolving complexity)

   

4. Finally, add the measure of Item Metrics > On Hand > Quantity on Hand.

   

5. The raw data should look similar to this:

   
   
   It should be viewing all inventory, have one line per item, one column per store, and be looking only at the stores you want to measure. 

Step Two: Table Calculation: Pivot_Where 

Consider the logic of what you want to find. You want to identify products that meet two criteria: 

• Criteria 1 is that the item is out of stock in the retail store
• Criteria 2 is that the item is in stock in the Warehouse 
   

Start with Criteria 1:

1. Open up a new Custom Field Table Calculation.

   

   

2. In the Table Calculation editor, add the table calculation: pivot_where 
    

   

3. Pivot_where needs two arguments: What is the logic for the column you are pivoting on? And which measure are you pulling from that column? 
   So the logic of the column is, you're looking at the Warehouse location (in this example and in the following image references, your store is titled Warehouse, but keep in mind that this will be text specific). 

   In the brackets, enter Store. 

   

   

4. Insert an equal sign after the shop name bracket

   

5. Then, between quotation marks, enter the name of the store (also note that this is case-sensitive).

   

6. Add a comma after the name of the store.

   

7. Finally, search for and enter the measure of Quantity on Hand.

   

   

8. Click Save. If prepared correctly, you'll have a single green calculation that returns the Quantity on Hand from the column with the Store titled Warehouse.

   

Step three: Warehouse Q > 0

Instead of seeing the exact quantity, the goal is to define whether there is any quantity in the warehouse yet or not. To achieve this, a calculation can be created to check for instances where the Quantity on hand is greater than zero.

1. Edit the new calculation to reflect this logic

   

2. Copy and paste the calculation you previously created for quantity in hand.

    

3. Include a greater than zero (> 0) 

   

4. Save. Now, instead of the numbers, there's a Yes if the Quantity on Hand is greater than 0, and a No if the Quantity on Hand is less than or equal to 0. This is the first set of criteria you're looking for.

   

Step four: Retail Q < 1

The second set of criteria that you want to check is whether the quantity in the retail store is 0 (or less). You can add this argument to your existing calculation. 

1. Edit the calculation again.

   

2. Below the argument, add the rule: AND

   

3. Then, to start the second argument, select all of your first argument and paste it below AND

   

   

4. Edit the store name from Warehouse to your retail store, for example, In The Woods.

   

   

5. Finally, to test the retail store quantity, change the zero tied to your retail store to one. 

   

6. Save and run the calculation. Now, you'll only see the Yes when both criteria are met.

   

Step Five: Hide NOs from Visualization

To complete the readability of the report,  give your New Calculation a more meaningful name.

1. New Calculation > Edit.

   

2. Below the code, in the editor, you'll see the Name field. Change the name to something for example Could we transfer?

   

3. Click Save, and the calculation title will be updated in the column.

   

4. Click the Settings (gear icon) > Hide "No"s from visualization.

   

5. The table will gray out the items that are returned as No. The visualization of the report will be only the items that you can transfer.
    

   

   
    

Considering the names of your stores, this is the frame for the calculation:

Simple Build 

pivot_where(${shops.name}="Warehouse",${cl_item_facts.quantity_on_hand})>0 AND pivot_where(${shops.name}="Retail Store",${cl_item_facts.quantity_on_hand})<1

Looking both Ways 

(pivot_where(${shops.name}="Warehouse",${cl_item_facts.quantity_on_hand})>0 AND pivot_where(${shops.name}="Retail Store",${cl_item_facts.quantity_on_hand})<1) OR (pivot_where(${shops.name}="Warehouse",${cl_item_facts.quantity_on_hand})<1 AND pivot_where(${shops.name}="Retail Store",${cl_item_facts.quantity_on_hand})>0) 

Checking for out of stock in either of two Retail Stores to Warehouse

pivot_where(${shops.name}="Warehouse",${cl_item_facts.quantity_on_hand})>0 AND (pivot_where(${shops.name}="Store 1",${cl_item_facts.quantity_on_hand}) <1 OR pivot_where(${shops.name}="Store 2",${cl_item_facts.quantity_on_hand})<1)

Checking for out of stock in both two Retail Stores to Warehouse

pivot_where(${shops.name}="Warehouse",${cl_item_facts.quantity_on_hand})>0 AND pivot_where(${shops.name}="Store 1",${cl_item_facts.quantity_on_hand}) <1 AND pivot_where(${shops.name}="Store 2",${cl_item_facts.quantity_on_hand})<1