GlideAggregate methods on currency or price fields

[Joshua Prakash]

When working with GlideAggregate on currency or price fields in ServiceNow, it's important to recognize that the calculations are performed on the reference currency values. The reference currency is a base value stored in the database, independent of what users see based on their session currency (the currency displayed according to their location or preference).

Example Scenario:

Imagine you have a table storing expenses in various currencies, and you want to aggregate those values, like calculating the total expenses. The expenses are stored in USD as the reference currency.

• Reference Currency (Base): USD (U.S. Dollar)
• User's Session Currency: EUR (Euro)

Data:

Expense ID Amount (Reference Currency: USD) Displayed Currency Conversion Rate (USD to EUR)

EXP001	100 USD	EUR	1 USD = 0.9 EUR
EXP002	200 USD	EUR	1 USD = 0.85 EUR

 

Aggregation:

Suppose you use GlideAggregate to get the SUM of these expenses:

var ga = new GlideAggregate('expenses_table');

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

ga.query();

if (ga.next()) {

var totalAmount = ga.getAggregate('SUM', 'amount');

gs.info('Total Amount in Reference Currency (USD): ' + totalAmount);

}

• This script calculates the total amount of expenses in the reference currency, which is USD in this case.
• The total is 100 + 200 = 300 USD.

Displaying the Aggregated Value:

If you need to show this total to a user whose session currency is in EUR, you must convert the aggregated value back into EUR using the relevant conversion rate. However, conversion rates can fluctuate, and the conversion rates used at the time of the individual expense entries may differ from the rate used during aggregation.

Conversion Example:

1. Conversion Rate When Calculating Aggregate: Suppose the current conversion rate is 1 USD = 0.88 EUR.

   • The system converts 300 USD to 300 * 0.88 = 264 EUR.

2. Expected User Value: If a user expected each expense to be converted using the rates at the time they were entered, they would calculate:

   • 100 USD * 0.9 = 90 EUR for the first expense.
   • 200 USD * 0.85 = 170 EUR for the second expense.
   • Total = 90 EUR + 170 EUR = 260 EUR.

Discrepancy:

• The system displays 264 EUR using the conversion rate during the aggregation.
• The user might expect 260 EUR as the total, considering the historical conversion rates of the individual transactions.

Key Takeaway:

This example highlights the potential differences between the reference currency values used in aggregation and the conversion to the user's session currency. It emphasizes why converting values using the proper rates during display is crucial, as it can lead to discrepancies that users might not expect.