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
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 |
| |