SET ANALYSIS Issue

[Anonymous]

I have the following expressions, which is not providing the correct result when used with SET ANALYSIS:

SUM(
{< DfPeriod = , fxPeriod = >}
AGGR(SUM(
IF(AGGR(SUM(PriorExposure), Region, ReportingPeriod, Amount, Type) = 0,
       AGGR(SUM(CurrExposure), Region, ReportingPeriod, Amount, Type),
IF((AGGR(SUM(CurrExposure), Region, ReportingPeriod, Amount, Type) / AGGR(SUM(PriorExposure), Region, ReportingPeriod, Amount, Type)) > 1.2,
       (AGGR(SUM(CurrExposure), Region, ReportingPeriod, Amount, Type) - (1.2 * AGGR(SUM(PriorExposure), Region, ReportingPeriod, Amount, Type))),
0)))
*
ONLY(OpsFactor), Region, ReportingPeriod, Amount, Type))

I have included SET ANALYSIS, because regardless of what values a user selects for [DfPeriod] and [fxPeriod], I do not want the result of my expression to change. If I remove the set analysis, then the expressions works correctly, however, if a user selects filters for either [DfPeriod] or [fxPeriod], then the result is zero.

I think that this may have something to do with the AGGR() function, but would appreciate any help / suggestions as I do not know how to continue.

[zhadrakas]

remove the blanks between equal and field in your set analysis Statement:

{< DfPeriod= , fxPeriod= >}

[arvind_patil]

Hi Michael,

Please Try this:

=SUM(AGGR(SUM({<DfPeriod =,fxPeriod = >}

IF(AGGR(SUM(PriorExposure), Region, ReportingPeriod, Amount, Type) = 0,

       AGGR(SUM(CurrExposure), Region, ReportingPeriod, Amount, Type),

IF((AGGR(SUM(CurrExposure), Region, ReportingPeriod, Amount, Type) / AGGR(SUM(PriorExposure), Region, ReportingPeriod, Amount, Type)) > 1.2,

       (AGGR(SUM(CurrExposure), Region, ReportingPeriod, Amount, Type) - (1.2 * AGGR(SUM(PriorExposure), Region, ReportingPeriod, Amount, Type))),

0)))

*

ONLY(OpsFactor), Region, ReportingPeriod, Amount, Type))

Thanks,

Arvind Patil

[sunny_talwar]

Try this

Sum({<DfPeriod, fxPeriod>} Aggr(Sum({<DfPeriod, fxPeriod>}

If(Sum({<DfPeriod, fxPeriod>}PriorExposure) = 0, Sum({<DfPeriod, fxPeriod>}CurrExposure),
If((Sum({<DfPeriod, fxPeriod>}CurrExposure)/Sum({<DfPeriod, fxPeriod>}PriorExposure)) > 1.2, (Sum({<DfPeriod, fxPeriod>}CurrExposure) - (1.2 * Sum({<DfPeriod, fxPeriod>}PriorExposure))), 0)) * Only({<DfPeriod, fxPeriod>}OpsFactor)

, Region, ReportingPeriod, Amount, Type))