How to have part of a calculation have a higher AGGR level, within a AGGR calculation.

[Ja123__]

Hi All,

My calculation below needs a last level of refining. I have an overall aggregation on Client, Member and Investment Product level. (Green)

Then within the calculation I need the result to be zero when the sum of the indicator column is greater than or equal to 1 at a Client, Member level. (Blue)

I think I understand why it is still not working because of my overall AGGR being at a lower level. 

Is there another way around this?

Many thanks,

James

sum(AGGR(

{<  dteTerminationDate4INDICATOR = {'-1'}, dteTerminationDate3INDICATOR = {'-1'},  dteDJFNew = {[<=$(vEndDate)]}, fkiLedgerID = {'1010','1020'}, fkiDescriptionID = {">3"},fkiInvestmentPolicyINDICATOR = {'-1'} >    }

if(  aggr(sum( {dteEffectiveDate14={[<=$(vEndDate)]}}blnBankAccn),ClientID,MemberID)   >= 1  , 0,

sum ( {< dteEffectiveDate14 = {"<=$(vEndDate)"} >} numProductUnits14 ) * FirstSortedValue( {< dteDate16 = {"<=$(vEndDate)"} >}  numOfferPrice , - dteDate16 ) )

,ClientID, MemberID,InvestmentProductID         

))

[Gysbert_Wassenaar]

Try using the TOTAL qualifier:
If( sum(TOTAL <ClientID,MemberID> {dteEffectiveDate14={[<=$(vEndDate)]}}blnBankAccn) >= 1, 0, ...

[Ja123__]

Hi Gysbert, thanks for responding. I tried the following but get the same answer. Perhaps I am interpreting you wrong?
Essentially what still happens is that we include certain products for a member when it has the indicator as 0 and exclude when it has a 1, when it should give a zero result when ANY of the products for a member has the 1 indicator:

sum(AGGR(

{
< dteTerminationDate4INDICATOR = {'-1'}, dteTerminationDate3INDICATOR = {'-1'}, dteDJFNew = {[<=$(vEndDate)]}, fkiLedgerID = {'1010','1020'}, fkiDescriptionID = {">3"},fkiInvestmentPolicyINDICATOR = {'-1'}
>
}

if( aggr(sum( TOTAL {dteEffectiveDate14={[<=$(vEndDate)]}}blnBankAccn),ClientID,MemberID) >= 1 , 0,

sum ( {< dteEffectiveDate14 = {"<=$(vEndDate)"} >} numProductUnits14 ) * FirstSortedValue( {< dteDate16 = {"<=$(vEndDate)"} >} numOfferPrice , - dteDate16 ) )

,ClientID, MemberID,InvestmentProductID

))

[Gysbert_Wassenaar · Accepted solution]

Ok, I guess my instruction to replace part of the expression was too obtuse.

Try:

sum(AGGR(

{< dteTerminationDate4INDICATOR = {'-1'}, dteTerminationDate3INDICATOR = {'-1'}, dteDJFNew = {[<=$(vEndDate)]}, fkiLedgerID = {'1010','1020'}, fkiDescriptionID = {">3"},fkiInvestmentPolicyINDICATOR = {'-1'} > }

if( sum(TOTAL <ClientID,MemberID> {<dteEffectiveDate14={[<=$(vEndDate)]}>}blnBankAccn) >= 1 , 0,

sum ( {< dteEffectiveDate14 = {"<=$(vEndDate)"} >} numProductUnits14 ) * FirstSortedValue( {< dteDate16 = {"<=$(vEndDate)"} >} numOfferPrice , - dteDate16 ) )

,ClientID, MemberID,InvestmentProductID

))