It’s been a while since I found an interesting topic, worthy of a blog article… This Q-Tip originated from a question that we received at the Masters Summit for Qlik in Ausitn, TX. Here is the question:
Hmm… at first I was puzzled and couldn’t even come up with a direction for the possible solution. How do I select Accounts that hold only the selected Assets and don’t hold any other assets? Several forms of Advanced Search passed by in my mind, but none of them offered a hint to the possible solution.
Then, I recalled an old linguistic joke:
However,” he pointed out, “there is no language wherein a double positive can form a negative.”
A voice from the back of the room piped up, “Yeah, right.”
Then, the joke led me to the solution. Double negative forms a positive! Instead of trying to select accounts that only hold selected Assets, we can exclude Accounts that hold excluded Assets. The remaining Accounts will naturally hold only selected Assets and nothing else. Here is the Set Analysis formula for this solution, that ended up to be fairly compact and elegant:
Exclude the excluded – double negative had formed a positive!
If you like the level of depth that you see in these articles, please check out the agenda of the Masters Summit for Qlik and find out if it’s right for you. This year, all three summits that we had in Milan, in Johannesburg, and in Austin, were extremely successful. Next year’s locations and dates are still being sorted out, but rumor has it that we might be in Germany in April, in Boston in September-October, and possibly in Brazil in June. Stay tuned!
2 Comments
this is a really smart way to solve it! thanks Oleg! I did not realize you can nest e() like that
[…] Excluding the Excluded. There are situations where after a user makes selections in one list box, we might want to limit the selections in another list box to the associated values, therefore excluding the excluded values. The easiest way to do that is to use a little set analysis. only({<Account=E({<Asset=E(Asset)>})>} Account). A neat little trick. You can see the entire post at Natural Synergies. […]