Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

Set Analysis. Final exclusion operator, with sum AGGR.

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

 

 

1 Solution

Accepted Solutions
Partner
Partner

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"} >

View solution in original post

2 Replies
Partner
Partner

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

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"} >

View solution in original post