Aggregator table

The aggregator table is refreshed daily, and allows quick access to important metrics that are otherwise slow to calculate.

Field name	Description	mSupply Use	Reference link	Type	Indexed
ID	UUID of the Aggregator Not use is UI	Long integer
storeID	Store identifier	used for dashboard reports	https://docs.msupply.org.nz/web_interface:dashboard_setup?s[]=aggregator#available_dashboard_reports	text
itemID	Item identifier	text
yearMonth	Month and year	text
fullDate	Full transaction/record date	date
dataElement	Name of the metric	text
value	Actual measured value	real
temp1	Temporary / auxiliary numeric field	real
temp2	Temporary / auxiliary numeric field	real
temp3	Temporary / auxiliary numeric field	real

Available dataElement values

This table holds aggregated values with different dimensions. For example, some values are aggregated per store, others per store and item, and others per store, item, and yearmonth.

Value	StoreID	ItemID	datetime	Comment
monthlyConsumption	✓	✓	yearmonth	Sums consumption for each month there is consumption Consumption in this case = (transaction.type = 'ci' and transaction.status in ('cn', 'fn'))
AMC	✓	✓	-	Sums all consumption (using dataElement = 'monthlyConsumption') and divides by number of months with consumption
stockConsumption	✓	✓	fulldate	For every day there is a customer invoice, aggregate consumption
stockMovement	✓	✓	fulldate	For every day there is stock movement, sums up supply and subtracts sum of consumption
stockHistory	✓	✓	fulldate	Reverse ledger (using dataElement = 'stockMovement'), records stock level at the end of each day where stock movement existed
monthlyDOS	✓	✓	yearmonth	Calculate number of day per month where stock was 0
realAMC	Formula for realAMC = Consumption Sum / (Number of Months With Consumption - (totalDOS/30.5))