Aggregation Expression

[#1]

Hello Folks

I have a problem with the Aggr function that for some reason returns a result of zero.

The following formula returns results:

=Num(
(
 (
 // FMV Movement at the Investment Currency
 (
 Sum ( _rFMV )
 *
 Max(FX_fromUSD_ToInvCcy_RepDate)
 )
 -
 (
 Sum({$<DT_ReportDate={"$(vCompDate)"},DT_ComparisonDate={"*"}>} _rFMV )
 *
 Max({$<DT_ReportDate={"$(vCompDate)"},DT_ComparisonDate={"*"}>} FX_fromUSD_ToInvCcy_RepDate )
 )
 +
 (
 // Current Period Distributions at Investment Currency
 Sum ( _iDist )
 -
 // Prior Period Distributions at Investment Currency
 Sum ( { $ <DT_ReportDate={"$(vCompDate)"},DT_ComparisonDate={"*"} > } _iDist )
 )
 -
 (
 // Current Period Cost at Investment Currency
 Sum ( _iCost )
 -
 // Prior Period Cost at Investment Currency
 Sum ( { $ <DT_ReportDate={"$(vCompDate)"},DT_ComparisonDate={"*"} > } _iCost )
 )
 )
 //Prior Period FX Rate at the Investment Currency
 /
 Max ( { $ <DT_ReportDate={"$(vCompDate)"},DT_ComparisonDate={"*"} > } FX_fromUSD_ToInvCcy_RepDate )
 )
/vDivisor
 ,vNumberFormat
 )

My problem though, is that I need to aggregate this over a currency field (the only dimension I have in the table is RG_Investment, I also need to aggregate over IT_InvCcy) so I thought the following would work:

=Num(
// Sum(
// Aggr(
 (
 (
 // FMV Movement at the Investment Currency
 (
 Sum ( _rFMV )
 *
 Max(FX_fromUSD_ToInvCcy_RepDate)
 )
 -
 (
 Sum({$<DT_ReportDate={"$(vCompDate)"},DT_ComparisonDate={"*"}>} _rFMV )
 *
 Max({$<DT_ReportDate={"$(vCompDate)"},DT_ComparisonDate={"*"}>} FX_fromUSD_ToInvCcy_RepDate )
 )
 +
 (
 // Current Period Distributions at Investment Currency
 Sum ( _iDist )
 -
 // Prior Period Distributions at Investment Currency
 Sum ( { $ <DT_ReportDate={"$(vCompDate)"},DT_ComparisonDate={"*"} > } _iDist )
 )
 -
 (
 // Current Period Cost at Investment Currency
 Sum ( _iCost )
 -
 // Prior Period Cost at Investment Currency
 Sum ( { $ <DT_ReportDate={"$(vCompDate)"},DT_ComparisonDate={"*"} > } _iCost )
 )
 )
 //Prior Period FX Rate at the Investment Currency
 /
 Max ( { $ <DT_ReportDate={"$(vCompDate)"},DT_ComparisonDate={"*"} > } FX_fromUSD_ToInvCcy_RepDate )
 )
 ,RG_Investment,IT_InvCcy
 )
 )
 /vDivisor
 ,vNumberFormat
 )

This just returns results of zero, and I'm completely lost with it.

Notes:

vDivisor is simply a unit divider that is set to either 1, 1000 or 1000000

vNumberFormat is the decimal formatting, currently set to #,##0;(#,##0);-

Any help on this would be greatly appreciated as I'm rapidly losing the will to live.

[#2 · Accepted solution]

Hi Folks

First, apologies for the formatting on the above, difficult getting that stuff right.

Anyway, I figured it out, it was nothing to do with the formula anyway, the data model behind it was causing the issue by returning NULL values for some transactions at certain currencies.