System Formulas

Systems Management Fields with Formulas and Data Sources

Primary KPIs (Dashboard Level)

1. Total Systems

• Formula: COUNT(System_ID)
• Data Source: Systems_Table
• Description: Total count of all systems regardless of status

2. Operational Systems

• Formula: COUNT(System_ID) WHERE Status = 'Operational'
• Data Source: Systems_Table (Status field)
• Description: Count of systems with operational status

3. Under Maintenance Systems

• Formula: COUNT(System_ID) WHERE Status = 'Under Maintenance'
• Data Source: Systems_Table (Status field)
• Description: Count of systems temporarily unavailable for maintenance

4. High Criticality Systems

• Formula: COUNT(System_ID) WHERE Criticality IN ('High')
• Data Source: Systems_Table (Criticality field)
• Description: Number of systems with high or very high criticality ratings

Systems Registry Table Fields

5. Current Throughput

• Formula: Reading From Flow Capacity Service Order
• Data Source: From Flow Capacity Service order that are created for this system
• Description: Current operational output of the system

6. System Age

• Formula: Current Date - Commission Date (in years)
• Data Source: Systems_Table (Commission_Date field)
• Description: Operational age since commissioning

System Detail View - Key Metrics

7. Condition Score

• Formula: average of condition scores of all child assets belonging to this system
• Data Source: Assets table (Condition_Score field for all system assets)
• Description: Overall physical health of the system

8. Risk Score

• Formula: Final calculated score from the average risk score of all assets in this system
• Data Source: Asset Risk Score
• Description: Overall risk associated with the system

9. Efficiency

• Formula: (Current Throughput / Design Capacity) × 100
• Data Source:
  • From the service order with the operation type flow capacity
  • Systems_Table (Design_Capacity field)
• Description: Percentage utilization with progress bar visualization

10. Current Capacity

• Formula: Current Throughput / Design Capacity
• Data Source:
  • From the service order with the operation type flow capacity
  • Systems_Table (Design_Capacity field)
• Description: Percentage utilization with progress bar visualization

11. Operational Performance Score

• Formula: Average Performance Index of all assets under that system
• Data Source:
  • Performance index of assets formula ticket released
• Description: Performance

12. Critical Alerts Count

• Formula: COUNT(Alert_ID) WHERE System_ID = [This System] AND Status = 'Active' AND Priority IN ('High', 'Critical')
• Data Source: Alerts/System_Alerts table
• Description: Number of active high-priority alerts

13. Associated Assets Count

• Formula: COUNT(Asset_ID) WHERE Parent_System_ID = [This System_ID]
• Data Source: Assets table
• Description: Total number of child assets in the system

Performance Tab Metrics

14. Total Assets in System

• Formula: COUNT(Asset_ID) WHERE Parent_System_ID = [Current_System_ID]
• Data Source: Assets table
• Description: Count of all assets belonging to the system

15. Inspection Compliance

• Formula: (COUNT(Completed Inspections) / COUNT(Scheduled Inspections)) × 100
• Data Source:
  • Service Orders table (inspection type)
  • Maintenance schedules
• Description: Percentage of scheduled inspections completed on time

16. Overall Risk Score

• Formula: average of risk scores of all assets
• Data Source:
  • Asset Registry table (Risk_Score)
  • Risk assessment records
• Description: Aggregated facility risk combining probability and consequence

17. Service Level Achievement

• Formula: (Times Met Service Level / Total Times) × 100
• Data Source:
  • When service orders with the type of water quality are completed and approved calculate total number of times that type service orders is completed which becomes your total times and in those total times calculate the number of time all the readings of the service order are in range that will be your times met service level
• Description: Percentage of time service targets were met

18. Portfolio Failure Rate

• Formula: (Number of Failed Assets / Total Assets in System) × 100
• Data Source:
  • Assets table (failure records)
  • Asset status tracking
• Description: Percentage of assets with recorded failures

19. Average MTBF (Mean Time Between Failures)

• Formula: AVG(Asset_MTBF) for all child assets
• Data Source:
  • Assets table (MTBF calculations)
  • Failure history records
• Description: Average time between failures for system assets

Energy Consumption Metrics

20. Energy Efficiency

• Formula: Total kWh Consumed ÷ Total Megaliters (ML) Produced
• Data Source:
  • Total Kwh Consumed can be Picked from the bill uploaded, total Megaliters produced is sum of capacities of all the flow capacity service orders
• Description: Energy used per unit of output (kWh/ML)

21. Monthly Energy Cost

• Formula: Directly fetched from the bill uploaded
• Data Source:
  • Energy monitoring systems
  • Utility billing data
• Description: Total energy cost for current period

Finance Tab Calculations

22. Current Book Value

• Formula: sum of current value of all the assets in the system
• Data Source:
  • Asset current value
• Description: Net accounting value

23. Annual Depreciation

• Formula: sum of(Initial Capital Cost - Salvage Value) / Design Useful Life (Years) for all assets
• Data Source:
  • Initial_Capital_Cost from each asset, Design_Useful_Life from each asset, Depreciation_Schedule from facility add from
  • Salvage_Value (currently 0)
• Description: Yearly depreciation expense

24. Depreciation To Date (Current Year)

• Formula: Annual Depreciation × (Months Passed in Fiscal Year / 12)
• Data Source:
  • Calculated annual depreciation
  • Current fiscal year calendar
• Description: Year-to-date depreciation recognized

25. Remaining Depreciation

• Formula: Current Book Value - Salvage Value
• Data Source:
  • Calculated current book value
  • Systems_Table (Salvage_Value)
• Description: Future depreciation to be charged

Alerts Tab Calculations

26. Emergency Work Orders Count

• Formula: COUNT(SO_ID) WHERE System_ID = [Current_System_ID] AND Type = 'Reactive' AND Status != 'Completed'
• Data Source: Service Orders table
• Description: Open emergency work orders requiring immediate attention

27. SLA Breaches Count

• Formula: COUNT(SLA_Breach_ID) WHERE System_ID = [Current_System_ID] AND Status = 'SLA Breached'
• Data Source: SLA breach tracking records
• Description: Service level agreement violations

28. Overdue Tasks Count

• Formula: COUNT(SO_ID) WHERE System_ID = [Current_System_ID] AND Due_Date < Current_Date AND Status != 'Completed'
• Data Source: Service Orders table
• Description: Work orders past due date

29. Work Order Backlog Hours

• Formula: SUM(Estimated_Hours) WHERE System_ID = [Current_System_ID] AND Status = 'Created, Assigned and Inprogress'
• Data Source: Service Orders table (Estimated_Hours field)
• Description: Total labor hours for open work orders

Compliance Tab Metrics

30. Samples Taken (Current Month)

• Formula: COUNT(Water Quality Service Orders) WHERE Sample_Date IN current month AND System_ID = [Current_System_ID]
• Data Source: Water Quality Service Orders table
• Description: Monthly regulatory samples collected

31. Parameters Monitored Count

• Formula: COUNT(DISTINCT Parameter) for current system
• Data Source: Compliance Schedules table
• Description: Distinct water quality parameters monitored

32. Exceedances (Last 30 Days)

• Formula: COUNT(Samples) WHERE Result outside Range AND Sample_Date within last 30 days
• Data Source:
  • Water Quality Readings table
  • Regulatory standards table
• Description: Recent regulatory limit violations

Asset Health Metrics

33. Asset Health Status Distribution

• Formula: COUNT(Assets) WHERE Condition = '[Status]' and (Status Count / Total Assets) × 100
• Data Source: Assets table (Condition field)
• Description: Breakdown of assets by condition categories

34. Asset Age Profile Distribution

• Formula: COUNT(Assets) for each predefined age bracket
• Data Source:
  • Assets table (Commission_Date)
  • Age calculation: Current Date - Commission_Date
• Description: Assets grouped by age ranges

Criticality Assessment Calculation

35. Final Criticality Score

• Formula: (Impact_on_Supply × 0.30) + (Health_Risk × 0.25) + (Redundancy × 0.15) + (Operational_Dependency × 0.15) + (Customer_Impact × 0.15)
• Data Source: Criticality Assessment form inputs
• Description: Weighted score from five assessment dimensions

Schedules Tab Metrics

36. Schedule Runs Count

• Formula: Total number of completed work orders generated from this schedule
• Data Source:
  • Service Orders table
  • Schedule tracking records
• Description: Times scheduled maintenance has been completed

37. Schedule Progress

• Formula: (Number of Assets Completed / Total Assets in Schedule)
• Data Source:
  • Service Orders table (completion status)
  • Schedule asset assignments
• Description: Current cycle completion progress

38. Next Due Date Calculation

• Formula: Last Completed Date + Frequency
• Data Source:
  • Schedule configuration
  • Last completion records
• Description: Calculated next maintenance due date