Hello everyone,
I would like to know if I can filter my results by inventory location.
Basically, I need a report that gives me the starting stock, sales, and ending stock for a specific inventory location.
As it is now, the report shows the stock from all inventory locations, which makes it difficult to analyze performance by location.
I’m using ShopifyQL, and I’m not sure if I should be using inventory_by_location, inventory_location_name, or another field to achieve this.
Any guidance or examples would be appreciated!
FROM inventory_by_location
SHOW ending_inventory_units_at_location
WHERE inventory_location_name = '01 - Location'
GROUP BY product_variant_sku, product_title WITH TOTALS
SINCE startOfMonth(0m) UNTIL today
ORDER BY ending_inventory_units_at_location DESC
VISUALIZE ending_inventory_units_at_location
It should be possible to use:
WHERE inventory_is_tracked = true AND inventory_location_name = ‘01 - Location’
1 Like
Hi @Rufy_78 ,
Here’s a breakdown and guidance for what you want: a report showing starting stock, sales, and ending stock for a specific location.
-
What Table to Use
Use inventory_by_location — this is the correct table when you want to work with inventory quantities at different locations.
-
Filter by Location
Yes, inventory_location_name = ‘01 - Location’ is correct for filtering by location if your report supports it.
-
Trackable Inventory
You can add inventory_is_tracked = true to exclude non-tracked inventory, which is often useful.
Sample ShopifyQL Query:
FROM inventory_by_location
SHOW
starting_inventory_units_at_location,
sold_units_at_location,
ending_inventory_units_at_location
WHERE
inventory_is_tracked = true
AND inventory_location_name = ‘01 - Location’
GROUP BY
product_variant_sku,
product_title
WITH TOTALS
SINCE startOfMonth() UNTIL today
ORDER BY ending_inventory_units_at_location DESC
VISUALIZE ending_inventory_units_at_location
Note:
starting_inventory_units_at_location – Starting inventory during the selected time frame at the given location.
sold_units_at_location – Units sold at that specific location.
ending_inventory_units_at_location – Current stock level at that location.
inventory_location_name – Must exactly match the name of the location in Shopify.
inventory_is_tracked – Filters to only show tracked inventory.
Thanks
Helo @StevenT_A7
Thank you for your help. However, the field ‘sold_units_at_location’ is not available in the ‘inventory_by_location’ table. I believe this field is only found in the ‘inventory’ table, and I’m unable to join the two tables.
I tried this:
FROM inventory_by_location, inventory
SHOW starting_inventory_units_at_location, ending_inventory_units_at_location,
inventory_units_sold
WHERE inventory_is_tracked = true AND inventory_location_name = '01 - Location'
GROUP BY product_variant_sku, product_title WITH TOTALS
SINCE today UNTIL today
ORDER BY ending_inventory_units_at_location DESC
VISUALIZE ending_inventory_units_at_location
but the field ‘inventory_location_name’ is not found in the ‘inventory’ table.
In Shopify, the ending inventory value is available under the Inventory Reports section, but it typically updates only at midnight, so you’ll need to wait until then to view the latest figures.
With Report Pundit’s Inventory by Location report, you can view the current inventory value at each location whenever needed. Additionally, we can calculate the ending inventory value going forward for each location.
That said, I recommend using the current inventory value, as it reflects the most up-to-date figures.
@Rufy_78 where is inventory_by_location documented?
I couldn’t find anything under the ShopifyQL dataset documentation here https://shopify.dev/docs/api/shopifyql/datasets/orders-dataset
I only saw documentation for the following datasets: Orders, Products, and Payment Attempts.