topic Re: Issue with exclude in set analysis in QlikView

Issue with exclude in set analysis

Thu, 30 Jul 2015 14:20:54 GMT

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

Re: Issue with exclude in set analysis

ramoncova06 — Thu, 30 Jul 2015 14:58:56 GMT

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)

Re: Issue with exclude in set analysis

Thu, 30 Jul 2015 15:09:47 GMT

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

Re: Issue with exclude in set analysis

ramoncova06 — Thu, 30 Jul 2015 16:40:43 GMT

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

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