Issue with exclude in set analysis

[#1]

Hi sunindia maxgro RamonCova06 swuehl ashfaq_haseeb,

I have to calculate data from two pools. One is dynamic pool and the other one is static pool.

And I need to calculate some values for TX, so I included State = [TX] in my set expression.

But when I exclude TX from my current selection only values for TX from dynamic pool must be excluded.

The condition works fine for other states but in case 'TX', I'm having an issue.

For 'TX':

  SUM({<  [RAL State]={'TX'} ,  [Transaction ID]-={$(vTransactionIDsToExclude)},  $(vSecFilter)>

                +

                < [TransactionID]={$(vTransactionIDtoInclude)}, [RAL State]={'TX'}  >}[SEC Amort Discounted Balance])

                +

sum({1 < [ActiveFlag] = {'Active'} ,  [Portfolio Code]={'1'} , [Company Code]={'1'} , [Region Code]={'60'}, [RAL State]={'TX'} >}

                  [SEC Amort Discounted Balance] )

For other than 'TX':

SUM({<  [RAL State]-={'TX'} ,  [Transaction ID]-={$(vTransactionIDsToExclude)},  $(vSecFilter)>

                +

                < [TransactionID]={$(vTransactionIDtoInclude)}, [RAL State]-={'TX'}  >}[SEC Amort Discounted Balance])

                +

sum({1 < [ActiveFlag] = {'Active'} ,  [Portfolio Code]={'1'} , [Company Code]={'1'} , [Region Code]={'60'}, [RAL State]-={'TX'} >}

                  [SEC Amort Discounted Balance] )

Appreciate all the help. Thanks in advance.

Regards,

Sachin

[ramoncova06]

you need to add a if condition to check if texas is excluded or not and then use another expression

if (GetExcludedCount([RAL State]) <> 'TX', Expression one, Expresion two)

[#3]

Hi Ramon,

Do you mean something like this:

if(getexcludedcount([RAL State] <> 'TX'), SUM({< [RAL State]={'TX'}
, [Transaction ID]-={$(vTransactionIDsToExclude)} ,  $(vSecFilter)>
+
< [Transaction ID]={$(vTransactionIDtoInclude)} , [RAL State]={'TX'} >}[SEC Amort Discounted Balance] )
+

sum({1 < [Active Flag] = {'Active'} , [Portfolio Code]={'1'} , [Company Code]={'1'} , [Region Code]={'60'} , [RAL State]={'TX'} > }
[SEC Amort Discounted Balance],null() ) )

This won't work???

[ramoncova06]

so if I understood right, you have two expressions, one for TX and a second one for everyone else except TX, and if you exclude TX, then they should not get any value, is this right ?

if this is correct then yes I believe something like your expression should work, though you want the null () inside of the last ")"

if(getexcludedcount([RAL State] <> 'TX'), SUM({< [RAL State]={'TX'}
, [Transaction ID]-={$(vTransactionIDsToExclude)} ,  $(vSecFilter)>
+
< [Transaction ID]={$(vTransactionIDtoInclude)} , [RAL State]={'TX'} >}[SEC Amort Discounted Balance] )
+

sum({1 < [Active Flag] = {'Active'} , [Portfolio Code]={'1'} , [Company Code]={'1'} , [Region Code]={'60'} , [RAL State]={'TX'} > }
[SEC Amort Discounted Balance]),null() )