last 12 months query for custom records

Hafsa1 · 3 weeks ago

Hi

I have a custom table(x_app_cost_service) having several records with "period" in format "FY25: M09". attached scrrenshot.

I want to get all records for last 12 months.

suppose today is FY26:M02 (feb month 2026 ), it should calculate 12 months from "FY26:M01" (jan 2026 ) back wards.

Situation happens where some months data not available it should take next month/year data. There could be multiple records for same month. What will be the query to fetch. Because once I get the query I need to calculate the "cost_amount" field for all records. Any idea?

Juhi Poddar · 3 weeks ago

Hi @Hafsa1

Try this:

var table = "x_app_cost_service";

// STEP 1: Get last 12 DISTINCT available periods
var gr = new GlideRecord(table);
gr.addNotNullQuery("period");
gr.orderByDesc("period");   // works because format is consistent
gr.query();

var distinctPeriods = [];
var periodMap = {};

while (gr.next()) {

    var period = gr.getValue("period");

    if (!periodMap[period]) {
        periodMap[period] = true;
        distinctPeriods.push(period);
    }

    if (distinctPeriods.length == 12)
        break;
}

// STEP 2: Fetch all records for those 12 periods
var totalCost = 0;

if (distinctPeriods.length > 0) {

    var gr2 = new GlideRecord(table);
    gr2.addQuery("period", "IN", distinctPeriods.join(","));
    gr2.query();

    while (gr2.next()) {
        totalCost += parseFloat(gr2.getValue("cost_amount") || 0); //update the cost_amount field name
    }
}

gs.info("Periods considered: " + distinctPeriods.join(", "));
gs.info("Total cost: " + totalCost);

Hope this helps!

"If you found my answer helpful, please like and mark it as an "accepted solution". It helps future readers to locate the solution easily and supports the community!"

Thank You
Juhi Poddar

View solution in original post

palanikumar · 3 weeks ago

Hi,

You can use condition 'Period' greater than or is 'FY25:M01'

Thank you,

Palani

Thank you,
Palani

Tanushree Maiti · 3 weeks ago

Hi @Hafsa1

In the navigation, type x_app_cost_service.list

It will show records in list view.

Now click on (funnel icon) filter:

Add a condition: [Created] [on] [Last 12 months]. Alternatively, use [Created] [on] [Last 1 year].

Click on Run

Next on XXPeriod field you can see 3 vertical dot. click on it

Either group by XXPeriod

Or Sort XXPeriod (Sort a to Z to Z to A) as per your requirement

Please mark this response as Helpful & Accept it as solution if it assisted you with your question.
Regards
Tanushree Maiti
ServiceNow Technical Architect
Linkedin:

Hafsa1 · 3 weeks ago

@Tanushree Maiti @palanikumar

the field period is a string field can't use date value. Some king of scripting need to do but not sure how

Tanushree Maiti · 3 weeks ago

Hi @Hafsa1 ,

This kind of javascript (sample code) , you can use to convert string to date

var fyString = "FY26:M02";

// 1. Extract the year and month using regex or split
// Splits by "FY" and then ":" to get 26 and M02
var parts = fyString.split(/FY|:M/); // ["", "26", "02"]
var yearShort = parts[1]; // "26"
var month = parts[2]; // "02"

// 2. Format to a standard structure (assuming 2000s)
var fullYear = parseInt("20" + yearShort); // 2026
var monthIndex = parseInt(month) - 1; // Months are 0-11 in JS (Feb is 1)

// 3. Create the Date object (Year, Month, Day)
// Using 1st of the month
var dateObj = new Date(fullYear, monthIndex, 1);

Please mark this response as Helpful & Accept it as solution if it assisted you with your question.
Regards
Tanushree Maiti
ServiceNow Technical Architect
Linkedin: