GlideAggregate not working as expected

ShaidaC · yesterday

Hi,
I have a before insert business rule that calculates YTD for records in a fiscal year(Apr-march)
what i intent to do is, get the month of the current record and set flag to false for any record of that month. then I will calculate the Risk priority number(RPN ) for the fiscal year and calculate the YTD and store latest YTD in current record and set flag to true.

Now the problem is that the business rule works fine when i use GlideRecord but fails when I try to use GlideAggregate. The query just doesn't seem to execute and I am getting YTD value as 0. Someone please guide me on what am I doing wrong. I have been on it for hours. Also, RPN is also calculated using a before insert BR of low order than this. below are the two codes for comparison

1. Using glode record:

(function executeRule(current, previous) {

var created = new GlideDateTime(current.sys_created_on);

gs.info('Creation time of record' + created);

var month = parseInt(created.getMonthLocalTime(), 10); // 1–12

gs.info('Month of created record' + month);

var year = parseInt(created.getYearLocalTime(), 10);

gs.info('Year of created record' + year);

var now = new GlideDateTime();

var monthStart = new GlideDateTime(year + "-" + month + "-01 00:00:00");

var gr = new GlideRecord('x_upl2_zlp_audit_zlp_audit_creation');

gr.addQuery('sys_created_on', '>=', monthStart);

gr.addQuery('sys_created_on', '<=', now);

gr.addQuery('is_month_latest', true);

gr.query();

gs.info('Records with true flag' + gr.getRowCount());

while (gr.next()) {

gr.is_month_latest = false;

gr.update();

}

var fiscalYear = (month < 4) ? year - 1 : year;

var fiscalStart = new GlideDateTime(fiscalYear + "-04-01 00:00:00");

gs.info('Fiscal start is' + fiscalStart);

var fiscalMonth = month - 3;

if (fiscalMonth <= 0)

fiscalMonth += 12;

var ga = new GlideRecord('x_upl2_zlp_audit_zlp_audit_creation');

ga.addQuery('sys_created_on', '>=', fiscalStart.getValue());

ga.query();

gs.info('records found' + ga.getRowCount());

var totalRPN = 0;

while(ga.next()) {

gs.info('if loop entered');

totalRPN += ga.total_rpn;

}

gs.info('total RPN for this fiscal year' + totalRPN);

var ytd = totalRPN / fiscalMonth;

gs.info('YTD VALUE FOR THIS FISCAL YEAR' + ytd);

current.ytd = ytd;

current.is_month_latest = true;

})(current, previous);

2. Using glideaggregate:

(function executeRule(current, previous) {

var created = new GlideDateTime(current.sys_created_on);

gs.info('Creation time of record' + created);

var month = parseInt(created.getMonthLocalTime(), 10); // 1–12

gs.info('Month of created record' + month);

var year = parseInt(created.getYearLocalTime(), 10);

gs.info('Year of created record' + year);

var now = new GlideDateTime();

var monthStart = new GlideDateTime(year + "-" + month + "-01 00:00:00");

var gr = new GlideRecord('x_upl2_zlp_audit_zlp_audit_creation');

gr.addQuery('sys_created_on', '>=', monthStart);

gr.addQuery('sys_created_on', '<=', now);

gr.addQuery('is_month_latest', true);

gr.query();

gs.info('Records with true flag' + gr.getRowCount());

while (gr.next()) {

gr.is_month_latest = false;

gr.update();

}

var fiscalYear = (month < 4) ? year - 1 : year;

var fiscalStart = new GlideDateTime(fiscalYear + "-04-01 00:00:00");

gs.info('Fiscal start is' + fiscalStart);

var fiscalMonth = month - 3;

if (fiscalMonth <= 0)

fiscalMonth += 12;

// var ga = new GlideRecord('x_upl2_zlp_audit_zlp_audit_creation');

// ga.addQuery('sys_created_on', '>=', fiscalStart.getValue());

// ga.query();

// gs.info('records found' + ga.getRowCount());

// var totalRPN = 0;

// while(ga.next()) {

// gs.info('if loop entered');

// totalRPN += ga.total_rpn;

// }

// gs.info('total RPN for this fiscal year' + totalRPN);

var ga = new GlideAggregate('x_upl2_zlp_audit_zlp_audit_creation');

ga.addQuery('sys_created_on', '>=', fiscalStart.getValue());

ga.addAggregate('SUM', 'total_rpn');

ga.query();

var totalRPN = 0;

if (ga.next()) {

totalRPN = parseFloat(ga.getAggregate('SUM', 'total_rpn')) || 0;

}

gs.info('total RPN (GA) for this fiscal year: ' + totalRPN);

var ytd = totalRPN / fiscalMonth;

gs.info('YTD VALUE FOR THIS FISCAL YEAR' + ytd);

current.ytd = ytd;

current.is_month_latest = true;

})(current, previous);

Anupam1 · yesterday

Hi @ShaidaC ,

Differences Between GlideRecord and GlideAggregate

GlideRecord: Iterates through rows and lets you access fields directly (ga.total_rpn).
GlideAggregate: Only returns aggregate values (SUM, COUNT, AVG, etc.). You cannot access fields directly — you must use getAggregate() after calling addAggregate().

Issues in your GlideAggregate Code

Date comparison format

You’re using fiscalStart.getValue() which returns a string (yyyy-MM-dd HH:mm:ss).
GlideAggregate sometimes requires a GlideDateTime object instead of a string.
Try:
ga.addQuery('sys_created_on', '>=', fiscalStart);

Missing upper bound

In your GlideRecord version, you used both >= fiscalStart and <= now.
In GlideAggregate, you only used >= fiscalStart.
That means it’s pulling all records after fiscal start, possibly none if your test data is outside that range.
Add the upper bound:
ga.addQuery('sys_created_on', '<=', now);

Calling ga.next()

GlideAggregate returns one row per group. If you don’t group, you’ll only get one row with the aggregate.
Your if (ga.next()) is correct, but if no records match, it will never enter.
Add a debug log before ga.next() to confirm:
gs.info('Aggregate query executed, rows: ' + ga.getRowCount());

Field type of total_rpn

Ensure total_rpn is a numeric field (integer/decimal). If it’s a string, SUM will fail silently.
If it’s a string, you’ll need to cast/convert before summing.

Corrected GlideAggregate Example

Cleaned-up version of your aggregate logic:

var ga = new GlideAggregate('x_upl2_zlp_audit_zlp_audit_creation');

ga.addQuery('sys_created_on', '>=', fiscalStart);

ga.addQuery('sys_created_on', '<=', now);

ga.addAggregate('SUM', 'total_rpn');

ga.query();

var totalRPN = 0;

if (ga.next()) {

totalRPN = parseFloat(ga.getAggregate('SUM', 'total_rpn')) || 0;

}

gs.info('Total RPN (GA) for this fiscal year: ' + totalRPN);

var ytd = totalRPN / fiscalMonth;

gs.info('YTD VALUE FOR THIS FISCAL YEAR: ' + ytd);

current.ytd = ytd;

current.is_month_latest = true;

Debugging Tips

Log ga.getRowCount() after ga.query() to confirm records are found.
Log ga.getAggregate('SUM', 'total_rpn') before parsing to see if it’s null.
Double-check that total_rpn is populated before insert (since you said another BR calculates it).

In short:

Add the upper bound (<= now).
Pass GlideDateTime objects instead of .getValue().
Confirm total_rpn is numeric.

That should fix your issue with GlideAggregate returning 0.

If my response helped please mark it correct and close the thread so that it benefits future readers.

Best,

Anupam.

ShaidaC · yesterday

Hi Anupam,
I have updated the Business Rule accordingly

(function executeRule(current, previous) {

var created = new GlideDateTime(current.sys_created_on);

gs.info('Creation time of record' + created);

var month = parseInt(created.getMonthLocalTime(), 10); // 1–12

gs.info('Month of created record' + month);

var year = parseInt(created.getYearLocalTime(), 10);

gs.info('Year of created record' + year);

var now = new GlideDateTime();

var fiscalYear = (month < 4) ? year - 1 : year;

var fiscalStart = new GlideDateTime(fiscalYear + "-04-01 00:00:00");

gs.info('Fiscal start is' + fiscalStart);

var fiscalMonth = month - 3;

if (fiscalMonth <= 0)

fiscalMonth += 12;

var ga = new GlideAggregate('x_upl2_zlp_audit_zlp_audit_creation');

ga.addQuery('sys_created_on', '>=', fiscalStart);

ga.addAggregate('SUM', 'total_rpn');

ga.query();

gs.info('Rows found for fiscal year:' + ga.getRowCount());

var totalRPN = 0;

if (ga.next()) {

totalRPN = parseFloat(ga.getAggregate('SUM', 'total_rpn')) || 0;

}

gs.info('total RPN (GA) for this fiscal year: ' + totalRPN);

var ytd = totalRPN / fiscalMonth;

gs.info('YTD VALUE FOR THIS FISCAL YEAR' + ytd);

current.ytd = ytd;

current.is_month_latest = true;

current.update();

})(current, previous);
The query only shows 14 records found while there are 140 records in the current fiscal year. Not sure why this is happening. Also I have made this as an After BR to avoid any confusion

Anupam1 · yesterday

Hi @ShaidaC ,

The Reason why you see Only 14 Rows

GlideAggregate doesn’t return raw records.
It returns aggregate rows based on the grouping you’ve defined.

If you don’t call groupBy(), you’ll only ever get one row (the aggregate across all records).
If you do call groupBy(), you’ll get one row per group.

In your code, you only added:
ga.addAggregate('SUM', 'total_rpn');

That means GlideAggregate is calculating one SUM across all records.
So ga.getRowCount() will not equal the number of records in the fiscal year — it will equal the number of aggregate rows (usually 1, unless grouped).

The 14 you’re seeing is likely because you previously had a groupBy or another condition in place, or because the query is restricted by your date filter. If you expect 140 records, you need to check the date range.

Date Range Issue

In your GlideRecord version, you used:

gr.addQuery('sys_created_on', '>=', fiscalStart);

gr.addQuery('sys_created_on', '<=', now);

But in your GlideAggregate version, you only used:

ga.addQuery('sys_created_on', '>=', fiscalStart);

That means you’re pulling all records since fiscal start until now, but not restricting the upper bound. If some records are outside the fiscal year window (e.g., future-dated or test data), they won’t count the way you expect.

Fixes

Add the upper bound:

ga.addQuery('sys_created_on', '>=', fiscalStart);

ga.addQuery('sys_created_on', '<=', now);

Don’t rely on getRowCount() with GlideAggregate.
It doesn’t tell you how many records matched — only how many aggregate rows were returned.

If you want the raw record count, you need a separate GlideRecord query:

var grCount = new GlideRecord('x_upl2_zlp_audit_zlp_audit_creation');

grCount.addQuery('sys_created_on', '>=', fiscalStart);

grCount.addQuery('sys_created_on', '<=', now);

grCount.query();

gs.info('Actual record count: ' + grCount.getRowCount());

Keep the SUM logic clean:

var ga = new GlideAggregate('x_upl2_zlp_audit_zlp_audit_creation');

ga.addQuery('sys_created_on', '>=', fiscalStart);

ga.addQuery('sys_created_on', '<=', now);

ga.addAggregate('SUM', 'total_rpn');

ga.query();

var totalRPN = 0;

if (ga.next()) {

totalRPN = parseFloat(ga.getAggregate('SUM', 'total_rpn')) || 0;

}

Debugging Checklist

Log both ga.getAggregate('SUM', 'total_rpn') and grCount.getRowCount() to compare.
Confirm that total_rpn is a numeric field (integer/decimal). SUM won’t work on strings.
Make sure your fiscal year calculation (fiscalStart) is correct — April 1 of the fiscal year.

In short:

getRowCount() on GlideAggregate ≠ number of records.
Add the missing <= now filter.
Use GlideRecord if you need the raw count, GlideAggregate only for SUM/AVG/etc.

If my response helped please mark it correct and close the thread so that it benefits future readers.

Best,

Anupam.