Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
))
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
))
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
))