Reporting Calculations

[Matthew Moore]

Hello,

I am working on a scheduled script execution to determine the variance from last week's forecasted revenue, and actual revenue for the same period. I am attempting to populate a single score report with a percentage in a custom field (u_variance_percentage) for the aggregation average. I am querying the time_card table for total billable hours for the previous week, and the resource_allocation table for allocated hours during that period for my calculations. My calculations are somehow incorrect resulting in a negative result. I am uncertain of the issue causing this. Any help is appreciated!

[Mark Manders · Accepted solution]

(function executeScheduledJob() {
    var BILLABLE_RATE = 150; 

    // Get date ranges for the last week
    var startOfLastWeek = gs.beginningOfLastWeek();
    var endOfLastWeek = gs.endOfLastWeek();

    // Calculate Total Allocated Revenue
    var totalAllocatedHours = 0;
    var grResourceAlloc = new GlideRecord('resource_allocation');
    grResourceAlloc.addQuery('start_date', '<=', endOfLastWeek);
    grResourceAlloc.addQuery('end_date', '>=', startOfLastWeek);
    grResourceAlloc.query();
    while (grResourceAlloc.next()) {
        totalAllocatedHours += parseFloat(grResourceAlloc.getValue('allocated_hours')) || 0;
    }
    var totalAllocatedRevenue = totalAllocatedHours * BILLABLE_RATE;
    gs.log('Total Allocated Hours: ' + totalAllocatedHours, 'DebugScheduledJob');
    gs.log('Total Allocated Revenue: ' + totalAllocatedRevenue, 'DebugScheduledJob');

    // Calculate Total Actual Revenue
    var totalActualHours = 0;
    var grTimeCard = new GlideRecord('time_card');
    grTimeCard.addQuery('week_starts_on', '>=', startOfLastWeek);
    grTimeCard.addQuery('week_starts_on', '<=', endOfLastWeek);
    grTimeCard.addQuery('u_billable', 'billable'); // Ensure this field is case-sensitive
    grTimeCard.query();
    while (grTimeCard.next()) {
        totalActualHours += parseFloat(grTimeCard.getValue('total')) || 0;
    }
    var totalActualRevenue = totalActualHours * BILLABLE_RATE;
    gs.log('Total Actual Hours: ' + totalActualHours, 'DebugScheduledJob');
    gs.log('Total Actual Revenue: ' + totalActualRevenue, 'DebugScheduledJob');

    // Calculate Variance Percentage
    var actualVariancePercentage = totalAllocatedRevenue === 0 
        ? 0 
        : ((totalActualRevenue - totalAllocatedRevenue) / totalAllocatedRevenue) * 100;

    gs.log('Actual Variance Percentage: ' + actualVariancePercentage, 'DebugScheduledJob');

    // Update the custom field (example target table: single_score_report)
    var grReport = new GlideRecord('u_single_score_report');
    if (grReport.get('sys_id', 'YOUR_RECORD_SYS_ID')) { // Replace with your record sys_id
        grReport.setValue('u_variance_percentage', actualVariancePercentage.toFixed(2));
        grReport.update();
    }
})();

Can you try it with this and if it doesn't work add enough logging to it, to ensure that you get all the data correctly in the correct form (no strings where numbers are expected, etc):