Issue with exclude in set analysis

Fri, 24 Jul 2015 14:47:59 GMT

Below is my set analysis expression which works perfectly fine.

[Disc. Bal.]
/
SUM({< [Transaction ID]-={$(vTransactionIDsToExclude)}
, $(vSecFilter)
>
+
<[Transaction ID]={$(vTransactionIDtoInclude)}>
+
< [Active Flag] = {'Active'}
, [Portfolio Code]={'1'}
, [Company Code]={'1'}
, [Region Code]={'60'}>

} TOTAL( [SEC Amort Discounted Balance]))

[Disc. Bal.] contains following expression:

SUM({<
[Transaction ID]-={$(vTransactionIDsToExclude)}
, $(vSecFilter)
, [Original Term]={'>72'}>
+
< [Transaction ID]={$(vTransactionIDtoInclude)}
, [Original Term]={'>72'}>}[SEC Amort Discounted Balance] )
+
sum({1< [Active Flag] = {'Active'}
, [Portfolio Code]={'1'}
, [Company Code]={'1'}
, [Region Code]={'60'}
, [Original Term]={'>72'} >
}
[SEC Amort Discounted Balance] )

But I now have a new requirement to exclude current selection. So I added '1' but now I'm getting very wierd results. Can anyone explain me the issue in my expression
Note: I only want to exclude from Active Flag, Portfolio COde, Company Code and Region Code.

[Disc. Bal.]
/
SUM({< [Transaction ID]-={$(vTransactionIDsToExclude)}
, $(vSecFilter)
>
+
<[Transaction ID]={$(vTransactionIDtoInclude)}>} TOTAL( [SEC Amort Discounted Balance]))

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

Regards,

Sachin

Re: Issue with exclude in set analysis

swuehl — Sat, 25 Jul 2015 08:39:49 GMT

You've split your one sum into two, why have you done this?

And now only the first sum acts as divisor for the [Disc. Bal.] dividend.

All in all, a quite different expression. Won't be suprised if you get very different results.

Not sure what you are really trying to achieve, creating a correct set expression in complex cases as you seem to work on needs a thorough understanding of the data model. So I can't really tell more.

topic Issue with exclude in set analysis in QlikView

Issue with exclude in set analysis

Re: Issue with exclude in set analysis