Set Analysis. Final exclusion operator, with sum AGGR.

[Ja123__]

Hi Guys

I have many different conditions for a calculation Exit Members.

count( distinct {

< CONDITION 1>
+
< CONDITION 2 >

-

<CONDITION 3> (New exclusion I want to bring in)

} MemberID )

I have a working calculation for FundBalance for each member. Now I want to use this calculation below, as the final condition it excludes members with a sum of the below less than 1.

The calc is quite long!!! It is two sums that I want to add together to give me the total fund credit for each member.

sum(AGGR({
< dteTerminationDate4INDICATOR = {'-1'}, dteTerminationDate3INDICATOR = {'-1'}, dteDJFNew = {[<=$(vEndDate)]}, fkiLedgerID = {'1020'}, fkiDescriptionID = {">3"},
fkiInvestmentPolicyINDICATOR = {'-1'}, fkiDescriptionID = {'4','11','12'} , dteEffectiveDate14 = {[<=$(vEndDate)]}>
+
< dteTerminationDate4INDICATOR = {'-1'}, dteTerminationDate3INDICATOR = {'-1'}, dteDJFNew = {[<=$(vEndDate)]}, fkiLedgerID = {'1010'}, fkiDescriptionID = {">3"},
fkiInvestmentPolicyINDICATOR = {'-1'}, fkiDescriptionID = {'8','40'} , dteEffectiveDate14 = {[<=$(vEndDate)]} >
}

sum( if(blnBankAccn = 1, 0, numAmount14 ) * if(fkiDescriptionID = 8,-1,if(fkiDescriptionID=40,-1,1)) )

,ClientID, MemberID,InvestmentProductID))

+

sum(AGGR(

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

if( sum( if(blnBankAccn = 1, 0, numProductUnits14 )) = 0, sum(numProductUnits14 ),

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

,ClientID, MemberID,InvestmentProductID))

[Ja123__]

Iv tried with an additional MemberId = {"=sum()<1"} and also tried keeping it in one line. Only " " on the outer sides with [] on the inside. Im sure its something again with the aggr that I'm missing

[Ja123__ · Accepted solution]

I basically looked at it again and wrote it in the following format and it worked!!!

Hopefully this will assist someone else:

< MemberID = {"=sum(AGGR({<dteTerminationDate4INDICATOR={'-1'},dteTerminationDate3INDICATOR={'-1'},dteDJFNew={[<=$(vEndDate)]},fkiLedgerID={'1020'},fkiDescriptionID={[>3]},fkiInvestmentPolicyINDICATOR={'-1'},fkiDescriptionID={'4','11','12'},dteEffectiveDate14={[<=$(vEndDate)]}>+<dteTerminationDate4INDICATOR={'-1'},dteTerminationDate3INDICATOR={'-1'},dteDJFNew={[<=$(vEndDate)]},fkiLedgerID={'1010'},fkiDescriptionID={[>3]},fkiInvestmentPolicyINDICATOR={'-1'},fkiDescriptionID={'8','40'},dteEffectiveDate14={[<=$(vEndDate)]}>}sum(if(blnBankAccn=1,0,numAmount14)*if(fkiDescriptionID=8,-1,if(fkiDescriptionID=40,-1,1))),ClientID,MemberID,InvestmentProductID))<1"} >