Performing operations on metrics

Every metric has a supportedOperations list that you can see when you call the GetAllQueryableKeys API. For example, Valuation/PV is defined as follows:

{
  "addressKey": "Valuation/PV",
  "description": "The present value of a holding of an instrument, at a point in time, in the settlement currency if applicable, otherwise domestic currency. Includes accrued interest",
  "displayName": "PV",
  "type": "Result0D",
  "flattenedType": "Decimal",
  "scalesWithHoldingQuantity": true,
  "supportedOperations": "Sum,DefaultSum,Proportion,Average,Count,Min,Max,Value,Append,SumOfPositiveValues,SumOfNegativeValues,SumOfAbsoluteValues,ProportionOfAbsoluteValues,SumCumulativeInAdvance,SumCumulativeInArrears",
  "lifeCycleStatus": "Experimental"
}

When you request a metric in a valuation report, you pair its key with one operation in the op field. You can always request a metric's Value. Text, date and true/false metrics also support Count. Numeric metrics support the full set of operations below.

How operations work

Each operation is applied to a group of rows:

• If you specify groupBy in the valuation request, each group is the set of holdings that share the same values for your groupBy metrics. LUSID returns one row per group.

• If you omit groupBy, LUSID returns one row per holding. The whole report is treated as a single group: operations such as Sum calculate a grand total and repeat it on every row.

• If your valuation schedule spans several dates, the whole report covers every date. For example, with no groupBy, Sum adds up values across all the dates. To get one total per date, group by Valuation/EffectiveAt.

• Proportion operations always divide by the total for the whole report, not by the total for the row's own group.

• Filters are applied first. Operations only see the rows that pass any filters you specify in your valuation request.

Available operations

Consider the following portfolio with five positions, valued without groupBy. In portfolio currency (Valuation/PvInPortfolioCcy), their values are 1000, 1000, 800, 400 and -200 (a short position), so the total is 3000:

LUSID names each result column after the operation and metric, for example DefaultSum(Valuation/PvInPortfolioCcy). The exception is Value, which uses the metric key on its own.

Summing values in different currencies

LUSID cannot sum amounts in different currencies. If you request Sum, Average, Min or Max for a metric such as Valuation/PV and a group contains both GBP and USD amounts, LUSID leaves the cell empty. It also adds a message to the row's Aggregation/Errors metric, for example:

metadata mismatch Dim:0,Units:(USD) != Dim:0,Units:(GBP).

To total amounts across currencies, use a version of the metric that LUSID converts to a single currency:

• *InPortfolioCcy metrics, such as Valuation/PvInPortfolioCcy and Valuation/ExposureInPortfolioCcy, are safe to total within one portfolio. They're also safe across portfolios that share a base currency.

• *InReportCcy metrics, such as Valuation/PvInReportCcy, are always safe to total, including across portfolios with different base currencies, providing you set reportCurrency in your valuation request.

Note: LUSID only checks currencies. Plain numbers have no unit, so LUSID adds them without warning. For example, Sum of Holding/default/Units across a whole portfolio returns a number that mixes shares of different equities with units of cash. Only sum units when you've grouped by instrument.

Calculating totals, proportions and weights

There are four ways to report portfolio totals, and the percentage each holding contributes, in a valuation report:

• Operations (Sum, Proportion and so on): flexible, and they follow your grouping and filters.

• Total metrics (Valuation/TotalPortfolio* and Valuation/TotalGroup*): one whole-portfolio or whole-group total on every row, unaffected by filters.

• Weight metrics (Valuation/Weight and the Aggregation/*Weight metrics): a holding's or group's exposure as a share of the whole portfolio.

• Subtotals: set the equipWithSubtotals field in your valuation request to add a subtotal row for each group and a grand-total row to a grouped report, calculated using the operations specified.

Which approach to use

Total metrics

LUSID calculates each total metric from every holding in the portfolio before it applies your filters or groupBy. It then reports the same figure on every row for that portfolio and date. This makes the total useful for showing the whole portfolio's value next to a filtered selection of holdings, or as the denominator in a derived property.

Note the following:

• Request total metrics with Value. Because every row already carries the full total, Sum multiplies it by the number of rows. For example, in a group of three holdings, Sum of a 3000 total returns 9000.

• "Group" means a portfolio group, not a groupBy group. TotalGroup* metrics are only populated when you value a portfolio group. If you value single portfolios, they're empty.

• For group totals in portfolio currency, every portfolio in the group must share a base currency. Otherwise the cell is empty and Aggregation/Errors reports Mixed currencies in total. If base currencies differ, use the *InReportCcy metric and set reportCurrency in your valuation request.

• *InReportCcy metrics need a report currency. If you don't set reportCurrency, they come back empty.

• One failure empties the total. If any holding fails to value, the total is empty. To leave failed holdings out of the total instead, set the IgnoreErrors metric option to true, for example {"key": "Valuation/TotalPortfolioPvInPortfolioCcy", "op": "Value", "options": {"IgnoreErrors": true}}.

For example, valuing a GBP portfolio worth 3000 with a filter that keeps only its UK equity and GBP cash holdings returns:

Proportion measures each holding against the 1800 that passed the filter. The total metric still reports the whole portfolio.

Weight metrics

Valuation/Weight reports each holding's exposure in portfolio currency divided by the total PV of the whole portfolio. For a reference portfolio, it's the holding's units divided by the portfolio's total units.

The Aggregation weight metrics apply the same calculation to each row of your report. If you group your report, they add up each group first:

Note the following:

• Request these metrics with Value. LUSID calculates them for each group itself.

• Aggregation/NetExposureWeight gives the same result as Sum of Valuation/Weight, and Aggregation/GrossExposureWeight as SumOfAbsoluteValues of Valuation/Weight.

• Set the Benchmark metric option to true to calculate the weight using the portfolio's benchmark instead.

Subtotals

Set equipWithSubtotals to true in your valuation request to add subtotal rows to a grouped report. LUSID adds one row for each value at each groupBy level except the last, and one grand-total row. In each extra row, the groupBy metrics being totalled show __ALL__.

LUSID calculates every extra row from the holdings underneath it, using the operations you request. For example, valuing a GBP portfolio of five holdings grouped by currency and then by instrument returns:

Note the following:

• equipWithSubtotals has no effect without groupBy.

• LUSID returns the subtotal and grand-total rows after all the other rows, not next to the group they total.

• Operations work on the holdings underneath each row, not on the rows above it. For example, the GBP subtotal's Average is 1800 divided by three holdings. Proportion still divides by the total for the whole report, so the grand-total row shows 1.

• Total metrics and weight metrics work as usual: Valuation/TotalPortfolioPvInPortfolioCcy shows the portfolio total on every row, and Aggregation/NetExposureWeight shows each row's share, 1 on the grand-total row.

• Value is empty on subtotal rows for most metrics, because the holdings underneath have different values.

• Don't use SumCumulativeInAdvance or SumCumulativeInArrears with subtotals. On a subtotal row, they show the running total from the last holding in the group, not a meaningful figure.

Other Aggregation metrics