Also known as Inventory On Hand History. Template creates Tableau data source named
This data source reports the monthly beginning and ending inventory quantity and cost on hand for every item by every storage location (warehouse, location, site, facility, bin, etc). It also reports the monthly total Cost of Sales and overall net changes to inventory values. Together, these form the basis of average on hand calculations and turns.
Reports can include trends across the months (or groups of months), showing on hand (or average on hand if multiple months) and turns. Individual report values can be for one item at one location, or the totals and averages for summed groups of items.
Average On Hand
The data source provides a variety of methods for calculating the average quantity and cost on hand values. Be aware that all averages are based on monthly beginning and/or ending values. For the sake of data processing times and report performance on hand averages are not based on the daily fluctuations during the month.
The default (out of the box)
[Avg Cost OH] is the traditional formula for average on hand over a time period:
(Beginning Inventory + Ending Inventory)/2
Additional averaging variations are in the
Work: Base Formulas folder at the top of the data source. See Measure Definitions below.
[Inventory Turns] for a selected time period are:
SUM([Cost Of Sales] /
[Avg Cost OH].
[Avg Cost OH] is the traditional
[Avg Cost OH (Beg+End)/2].
[Monthly Turns By Avg Month End] (in the
Work: Base Formulas folder) uses
[Avg Cost OH (Sum)/Mo] instead of
[Avg Cost OH (Beg+End)/2].
The Turns calculation can be edited to use one of other averaging methods (Use Tableau Desktop to edit the data source, which will affect all reports using the edited measures OR while in report editing mode create another calculation which will be available for only that workbook) .
[Inventory Turns] in the Tableau data source in Tableau Desktop
[Avg Cost OH] to select a different averaging method. This is the measure used in
[Avg Cost OH] to the same method for the sake of consistency.
Creating a new measure
[Inventory Turns] as a model for creating a different Turns calculation.
Avg Cost/Qty OH (Beg+End)/2
The traditional method of averaging inventory values
Avg Cost/Qty OH (Beg+End)/Mo
averages each month’s beginning and ending balances, then averages that over the selected date range
Avg Cost/Qty OH (Sum)/Mo
Sum of all monthly ending balances (in the selected date range)
Avg Monthly Cost/Qty Sold
Sum of Sales / number of months
Beginning Cost/Qty OH
The On Hand at the beginning of the selected date range
Beginning Cost/Qty OH - Month
The OH at the beginning of every month in the selected date range. Using this in a column would sum all the months in that column.
On Hand at the end of the selected date range.
Cost/Qty OH - Month
On Hand at the end of every month in the selected date range. Using this in a column would sum all the months in that column.
Cost of Sales / Qty Sold
The monthly COGS or Qty, which will sum across the selected date range. Multiplied by -1 so that it displays as positive number.
The net of all transactions, which will sum across the selected date range.
Monthly Turns By Avg Month End
Alternative turn: uses
Number of Months
Count of the months in the selected date range. Used in averaging calculations.
Source of Inventory Data
Consult your DataSelf provider for more information about where your ERP stores its inventory history data. Some ERP’s have sales totals included in their monthly inventory history summary table.
But for ERP’s without monthly sales already summarized, DataSelf sums the sales type records from the inventory transaction history. For these ERP’s the SQL table
zLists_ERPName is a manually-editable list assigning inventory transaction types to sales and other groupings.