topic Re: SET ANALYSIS Issue in QlikView

SET ANALYSIS Issue

Anonymous — Wed, 26 Jul 2017 21:22:03 GMT

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.

Re: SET ANALYSIS Issue

zhadrakas — Thu, 27 Jul 2017 05:08:45 GMT

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

{< DfPeriod= , fxPeriod= >}

Re: SET ANALYSIS Issue

arvind_patil — Thu, 27 Jul 2017 05:37:15 GMT

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

Re: SET ANALYSIS Issue

sunny_talwar — Thu, 27 Jul 2017 11:35:34 GMT

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))