tables_fields:main_tables:aggregator

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision		 Previous revision
tables_fields:main_tables:aggregator [2020/01/07 01:04] – ↷ Page moved from tables:main_tables:aggregator to tables_fields:main_tables:aggregator loutables_fields:main_tables:aggregator [2026/01/28 04:42] (current) – Raksha Khadka
Line 3: Line 3:
 The aggregator table is refreshed daily, and allows quick access to important metrics that are otherwise slow to calculate. The aggregator table is refreshed daily, and allows quick access to important metrics that are otherwise slow to calculate.
  
-^ field ^ type ^ description ^ +^Field name ^ Description ^ mSupply Use ^ Reference link ^ Type ^ Indexed ^ 
-^ storeID  | text  | dimension|  +^ ID  |UUID of the Aggregator Not use is UI  | | | Long integer | | 
-^ itemID  | text  | dimension|  +^ storeID  |Store identifier |used for dashboard reports |https://docs.msupply.org.nz/web_interface:dashboard_setup?s[]=aggregator#available_dashboard_reports | text | |  
-^ yearMonth  | text  | dimension|  +^ itemID  |Item identifier | | | text | |  
-^ fullDate  | date  | dimension|  +^ yearMonth  |Month and year | | | text | |  
-^ dataElement  | text | name of the metric|  +^ fullDate  |Full transaction/record date | | | date | |  
-^ value real  | actual | value | +^ 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==== ====Available dataElement values====
  
-^ Value ^ Comment ^ +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. 
-^ 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 | +^ Value ^ StoreID ^ ItemID ^ datetime ^ Comment ^ 
-^ stockConsumption | For every day there is a customer invoice, aggregate consumption| +^ monthlyConsumption | ✓ | ✓ | yearmonth | Sums consumption for each month there is consumption    Consumption in this case = (transaction.type = 'ci' and transaction.status in ('cn', 'fn')) | 
-^ stockMovement | For every day there is stock movement, sums up supply and subtracts sum of consumption | +^AMC | ✓ | ✓ | - | Sums all consumption (using dataElement = 'monthlyConsumption') and divides by number of months with consumption | 
-^ stockHistory | Reverse ledger (using dataElement = 'stockMovement'), records stock level at the end of each day where stock movement existed | +^ stockConsumption | ✓ | ✓ | fulldate |For every day there is a customer invoice, aggregate consumption| 
-^ monthlyDOS| Calculate number of day per month where stock was 0 | +^ stockMovement | ✓ | ✓ | fulldate |For every day there is stock movement, sums up supply and subtracts sum of consumption | 
-^ realAMC | Formula for realAMC = Consumption Sum / (Number of Months With Consumption - (totalDOS/30.5)) |+^ 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 | 
 +^ <del>realAMC</del> | | | |<del>Formula for realAMC = Consumption Sum / (Number of Months With Consumption - (totalDOS/30.5))</del> |
  
  
 ---- struct data ---- ---- struct data ----
 +pagestatus.status    : 
 ---- ----
  
  • Last modified: 2020/01/07 01:04
  • by lou