Looking for an example script to perform QUARTERLY SLA calculations

[Adrian Weber1]

We are being asked to add an additional option to the Service Availability (service_availability) TYPE column.  Currently, there are 7 options there (Daily, Weekly, Monthly, Annually, Last 7 days, Last 30 days, and Last 12 months), but a new requirement has come down to use to create a "Quarterly" option.  

Adding that option to the choice list is easy... performing the calculations that go WITH it, however, is where we need assistance.  

For the purposes of this request, "Quarterly" is defined as a "calendar" quarter (as opposed to a financial quarter), so Q1 = Jan, Feb, March, Q2 = April, May, Jun, etc. 

I reached out to NOW support, and their response was this: 

"But these availability records get created by the "Calculate Availability" scheduled job which runs daily. There are various script includes that get called in this scheduled job. If you plan to add another choice, you will need to customize the script includes and make sure that the functionality is being achieved. Keep in mind this will be a total customization."

So, my question is:  Has anyone else done this before, and if so, can you share an example of your updated script?  We're working on a possible alternative resolution (where we define each month as a var (M1, M2, M3, etc.) and then just add them (M1 + M2 + M3), but I was just curious if anyone else had something we could jump-start off of. 

Thank you for any help you can provide! 

[Deepak Shaerma]

Hi @Adrian Weber1 

Adding a “Quarterly” option to the service_availability TYPE column in ServiceNow involves more than just expanding the choice list; it necessitates customizing the scheduled job and potentially the script includes that perform the calculation of service availability. Since this is a custom requirement, varying organizations may approach it differently based on their specific ServiceNow setup and business needs. I can guide you through a generic approach that you can tailor to fit your requirements.

### Approach for Adding “Quarterly” Service Availability Calculations

Step 1: Add “Quarterly” to the Choice List

You’ve mentioned this part is easy, but for completeness:

1. Navigate to the choice list for the service_availability table.
2. Add a new choice with the label “Quarterly” and an appropriate value.

Step 2: Identify Where Calculations are Made

ServiceNow mentioned that the “Calculate Availability” scheduled job is responsible for generating availability records and that various script includes are called by this job. Your first task is to review this scheduled job and identify the specific script includes or scripts called by it.

Step 3: Understand Current Calculation Logic

Before adding new logic for quarterly calculations, understand how current calculations (e.g., Daily, Weekly, Monthly) are being made. This typically involves aggregating or averaging data over the specified period. Review the existing scripts to understand:
- How time periods are defined.
- How data is aggregated or calculated for each time period.

Step 4: Implementing Quarterly Calculation Logic

1. Add a New Script Include or Update an Existing One: Depending on your findings from Step 2 and 3, you might need to create a new Script Include for the “Quarterly” calculations or update an existing one if it’s structured to handle multiple time periods.

2. Define Calendar Quarters: Since you need calendar quarters, your script will need to partition the year into four quarters (Q1 = Jan-Mar, Q2 = Apr-Jun, etc.).

3. Sample Quarterly Calculation Logic: Below is a simplified example of how you might begin structuring your Script Include for quarterly calculations. This is just a template and will need adjustments based on your actual data and calculation needs.

var QuarterlyAvailabilityCalculation = Class.create();
QuarterlyAvailabilityCalculation.prototype = {
    initialize: function() {
    },
    
    calculateForQuarter: function(quarter, year) {
        var startDate, endDate;
        
        switch(quarter) {
            case 1:
                startDate = year + ‘-01-01’;
                endDate = year + ‘-03-31’;
                break;
            case 2:
                startDate = year + ‘-04-01’;
                endDate = year + ‘-06-30’;
                break;
            case 3:
                startDate = year + ‘-07-01’;
                endDate = year + ‘-09-30’;
                break;
            case 4:
                startDate = year + ‘-10-01’;
                endDate = year + ‘-12-31’;
                break;
        }
        
        // Logic to calculate availability for the given date range
        // This can involve querying incidents, changes, or other records 
        // to determine availability, downtime, etc.
    },
    
    // Additional methods as needed for your calculations
};

// Example usage
var calc = new QuarterlyAvailabilityCalculation();
calc.calculateForQuarter(1, 2023); // For Q1 of 2023