PA Indicator historical rebuild

[Fabio Cresta]

Hi All,

I'm new with PA indicators. I need to recalculate the historical value from a table that contains a start date and a cost.

As an example:

RECORD 1: start date: 1/1/2025 - cost $100

RECORD 2: start_date 1/2/2025 - cost $100

I configured the indicator and the job, In the job I set as relative start 2 month (I'm currently in march) to include January and February.

The problem I have is that, running the Job manually, in the indicator scoresheets I see $100 in January for both records, while for RECORD 1 I should have $0 on January and $100 on February..

Am I missing sonthing?

Thanks

[AndersBGS]

Hi @Fabio Cresta ,

I would think you could utilize a scripted automated indicator to sum up the total cost if start date is or is after the current month calculated. Please note, i'm not a script expert, but below is generated based on AI - but please adjust accordingly if you're good at scripting:

(function executeRule(source, aggregatedValue) {
    var totalCost = 0;
    var indicatorMonth = source.getParameter('start'); // The month being calculated
    var indicatorMonthStart = new GlideDateTime(indicatorMonth);
    indicatorMonthStart.setDayOfMonthUTC(1); // Start of the month
    var indicatorMonthEnd = new GlideDateTime(indicatorMonthStart);
    indicatorMonthEnd.addMonthsUTC(1);
    indicatorMonthEnd.addDaysUTC(-1); // End of the month

    var threeMonthsAgo = new GlideDateTime(indicatorMonthStart);
    threeMonthsAgo.addMonthsUTC(-3); // Limit to last 3 months

    var gr = new GlideRecord('your_table'); // Replace with actual table name
    gr.addQuery('start_date', '>=', threeMonthsAgo); // Only consider last 3 months
    gr.addQuery('start_date', '<=', indicatorMonthEnd); // Ensure start date is within or before the current month
    gr.query();

    while (gr.next()) {
        totalCost += parseFloat(gr.total_cost); // Summing up total cost
    }

    aggregatedValue.setValue(totalCost);
})(source, aggregatedValue);

[Fabio Cresta]

I already defined a script. The job executes the script once for each record, so the valure you return is written in all periods...

[AndersBGS]

Hi @Fabio Cresta ,

 it’s because ServiceNow doesn’t know when the record was created / doesn’t have a fixed date field in the condition. Due to this, you will see the error (which is expected) for historical data collection where for future data collection your indicator can work just fine.

[Fabio Cresta]

Thanks Anders.

In reality the system has the created date, but anyway this is not the create life date to use. The system should have a parameter (or even allow by script) to check whether each record is at or after the passed data, otherwise the sum shown in the report is wrong. See the sample screenshot attached. 

I wonder if there's a way to achieve it.

Thanks