fcsc_ipi_backend/ipi-index-calculation-document.md

IPI Index Calculation Document

Purpose

This document explains what happens when runCompleteCalculation(year, month) is executed in app/services/ipi_calculation_service.js, including formulas, data flow, and output tables.

Runtime Entry Flow (Actual Start Point)

Calculation does not start by directly calling runCompleteCalculation from server.js. It starts through scheduler wiring:

1. server.js creates scheduler:
   • const scheduler = new AutomatedSchedulerService();
2. server.js starts jobs:
   • scheduler.start(); (IPI calculation schedule)
   • scheduler.startRemainderEmail(); (email reminders)
3. start() in app/services/scheduler.service.js registers cron:
   • cron.schedule('0 2 * * *', ...) => runs daily at 2:00 AM
4. Cron calls runScheduledTask()
5. runScheduledTask() flow:
   • fetch latest unprocessed quarter (ipi_calculated = 0)
   • check current date >= quarter end_date
   • run auto submission fill (executeSurveyAutoSubmit)
   • run IPI quarter calculation (executeCalculateQuarter)
   • set ipi_calculated = 1
6. executeCalculateQuarter(year, quarter) loops through quarter months and calls:
   • runCompleteCalculation(year, month) for each month

Manual trigger path also exists:

• POST /api/admin/trigger-scheduler -> scheduler.manualTrigger() -> runScheduledTask()

Calculation Pipeline Entry

Function: runCompleteCalculation(year, month)

Execution order:

1. aggregateMonthlyProduction(year, month)
2. calculateItemLevelIndices(year, month)
3. calculateISIC4DigitIndices(year, month)
4. calculateISIC3DigitIndices(year, month)
5. calculateISIC2DigitIndices(year, month)
6. calculateManufacturingIPI(year, month)

If any step throws an error, the pipeline stops and the error is propagated.

---

Data Prerequisite (Base Year)

Before running the monthly pipeline, base-year data must exist in base_year_production (typically by running calculateBaseYearProduction(2022)).

Base-year average production per product is calculated as:

BaseAvg_i = (Jan_i + Feb_i + Mar_i + Apr_i + May_i + Jun_i + Jul_i + Aug_i + Sep_i + Oct_i + Nov_i + Dec_i) / 12

Stored in:

• base_year_production.avg_by_production

Only approved submissions and active submission products are used.

---

Step-by-Step Flow in runCompleteCalculation

Step 1: Aggregate Monthly Production

Function: aggregateMonthlyProduction(year, month)

What it does:

• Maps the target month to quarter and period field:
  • Month 1 -> Q1/current_quantity_period_one
  • Month 2 -> Q1/current_quantity_period_two
  • ...
  • Month 12 -> Q4/current_quantity_period_three
• Sums product quantity across all approved submissions for that period.
• Writes one record per product to monthly_production.

Formula:

MonthlyProduction_i(y,m) = SUM( submission_products.period_quantity )

Output table:

• monthly_production(product_id, year, month, production_quantity, ...)

---

Step 2: Calculate Item Level Indices

Function: calculateItemLevelIndices(year, month)

What it does:

• Joins current month production with base-year average production by product.
• Excludes products where base average is zero or null (avg_by_production > 0).
• Stores item-level relative and item index.

Formulas:

Ri = CurrentProduction_i / BaseAvg_i

Ii = Ri * 100

Expanded:

ItemIndex_i(y,m) = (MonthlyProduction_i(y,m) / BaseAvg_i) * 100

Output table:

• item_level_indices(product_id, current_production, base_year_avg_production, production_relative, item_index, ...)

Interpretation:

• 100 means equal to base-year monthly average.
• >100 means above base-year average.
• <100 means below base-year average.

---

Step 3: Calculate ISIC 4-Digit Indices

Function: calculateISIC4DigitIndices(year, month)

What it does:

• Assigns each product to ISIC group using LEFT(establishments.isic_code, 4).
• Uses item-level index values belonging to that 4-digit group.
• Stores count and group index.

Implemented formulas:

total_weight_g = COUNT(DISTINCT product_id)

weighted_index_sum_g = SUM(ItemIndex_i)

ISIC4Index_g = AVG(ItemIndex_i)

Note:

• In this implementation, "weight" is the number of distinct products (equal weight per product), not an external official weight table.

Output table:

• isic_4digit_indices(isic_4digit_code, total_weight, weighted_index_sum, isic_4digit_index, ...)

---

Step 4: Calculate ISIC 3-Digit Indices

Function: calculateISIC3DigitIndices(year, month)

What it does:

• Rolls up 4-digit results to 3-digit groups via LEFT(isic_4digit_code, 3).
• Uses weighted average based on total_weight from 4-digit level.

Formulas:

W_h = SUM(total_weight_g) for all 4-digit groups g under 3-digit group h

WeightedSum_h = SUM(total_weight_g * ISIC4Index_g)

ISIC3Index_h = WeightedSum_h / W_h

Output table:

• isic_3digit_indices(isic_3digit_code, total_weight, weighted_index_sum, isic_3digit_index, ...)

---

Step 5: Calculate ISIC 2-Digit Indices

Function: calculateISIC2DigitIndices(year, month)

What it does:

• Rolls up 3-digit results to 2-digit groups via LEFT(isic_3digit_code, 2).
• Uses weighted average based on total_weight from 3-digit level.

Formulas:

W_k = SUM(total_weight_h) for all 3-digit groups h under 2-digit group k

WeightedSum_k = SUM(total_weight_h * ISIC3Index_h)

ISIC2Index_k = WeightedSum_k / W_k

Output table:

• isic_2digit_indices(isic_2digit_code, total_weight, weighted_index_sum, isic_2digit_index, ...)

---

Step 6: Calculate Manufacturing IPI (Headline)

Function: calculateManufacturingIPI(year, month)

What it does:

• Computes overall manufacturing index from all 2-digit ISIC groups.
• Then computes Month-on-Month (MoM) and Year-on-Year (YoY) percentage changes.
• Writes final result in manufacturing_ipi.

Headline formulas:

TotalWeight = SUM(total_weight_k)

TotalWeightedSum = SUM(total_weight_k * ISIC2Index_k)

ManufacturingIPI(y,m) = TotalWeightedSum / TotalWeight

Change formulas:

MoM(%) = ((IPI(y,m) - IPI(prev_month)) / IPI(prev_month)) * 100

YoY(%) = ((IPI(y,m) - IPI(y-1,m)) / IPI(y-1,m)) * 100

Reference date:

• First day of month: YYYY-MM-01

Output table:

• manufacturing_ipi(year, month, manufacturing_index, mom_change, yoy_change, total_weight, weighted_index_sum, status, generated_on, ...)

---

Logging and Transactions

Each calculation step:

• Opens its own DB transaction.
• Creates a start log entry in calculation_log.
• Updates log as Completed/Failed with record count.
• Commits on success and rolls back on error.

This means:

• A step is atomic by itself.
• The full pipeline is not one global transaction across all six steps.

---

End-to-End Formula Chain (Compact View)

1. BaseAvg_i = Avg monthly production in base year (2022)
2. ItemIndex_i = (CurrentProduction_i / BaseAvg_i) * 100
3. ISIC4Index = Avg(ItemIndex_i within ISIC4)
4. ISIC3Index = Weighted avg(ISIC4Index by ISIC4 total_weight)
5. ISIC2Index = Weighted avg(ISIC3Index by ISIC3 total_weight)
6. ManufacturingIPI = Weighted avg(ISIC2Index by ISIC2 total_weight)
7. MoM, YoY from prior stored headline indices.

---

Client Explanation Script (Short)

• We first aggregate monthly production quantity by product from approved submissions.
• Each product is compared against its base-year monthly average (2022), giving an item index where 100 equals base-year average output.
• Product indices are grouped by ISIC 4-digit, then rolled up to ISIC 3-digit and 2-digit using weighted averages.
• The final manufacturing IPI is the weighted average of all ISIC 2-digit indices.
• We then compute MoM and YoY growth rates using previously stored headline values.