Aggregator table
The aggregator table is refreshed daily, and allows quick access to important metrics that are otherwise slow to calculate.
| field | type | description |
|---|---|---|
| storeID | text | dimension |
| itemID | text | dimension |
| yearMonth | text | dimension |
| fullDate | date | dimension |
| dataElement | text | name of the metric |
| value real | actual | value |
Available dataElement values
| Value | Comment |
|---|---|
| monthlyConsumption | 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 | For every day there is a customer invoice, aggregate consumption |
| stockMovement | For every day there is stock movement, sums up supply and subtracts sum of consumption |
| stockHistory | Reverse ledger (using dataElement = 'stockMovement'), records stock level at the end of each day where stock movement existed |
| monthlyDOS | Calculate number of day per month where stock was 0 |
| realAMC | Formula for realAMC = Consumption Sum / (Number of Months With Consumption - (totalDOS/30.5)) |