Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
remove the blanks between equal and field in your set analysis Statement:
{< DfPeriod= , fxPeriod= >}
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
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))