Qlik Community

QlikView Documents

Documents for QlikView related information.

Ignore all selections except few fields using Set Analysis

MVP
MVP

Ignore all selections except few fields using Set Analysis

Hello,

This requirement has come up again and again and believe would keep coming up in QlikView/Qlik Sense development. Fortunately there are already some solutions been discussed and documented here in the community, like: Ignore all selections except some specific fields using Set Analysis which is very helpful. Somewhere, we people are tempted to find an easier work around if not a better solution.

Some of us tempt to use p() like: Sum({1<Field1=p(Field1)>} Amount)

expecting getting all amounts irrespective of all selections except Field1. However, this could lead to an unwanted output. Since, p(Field1), i.e. - possible values of Field1 could change with other selections, the output could get affected by other fields selections.

Let me explain with a case. Say I have a simple sales table like:

Untitled.png

With this, I want ignore all selections except Product. So I try with:

Sum({1<Product=p(Product)>}Sales)

When there is no selection I get as expected :1700

Untitled1.png

But what happens, if I select Year=2011 ? The value changes (see, below) which we never expected

Untitled3.png

Explanation: When I select Year=2011, the p(Product) returns possible values as Shirt and Jeans, and disregarding year selection, Shirt and Jeans overall Sales comes 500+600+200+300=1600. I.e. though the Year selection doesn't affect directly, but has reduced the possible values of Product.

However, the alternate solution I proposed - Sum({1<Product=$::Product>} Sales) works fine, because that is not related to possible values , rather the direct selection.

This came up while I was trying to help with a similar requirement here :Set Analysis

And yes, the credit goes to Simen Kind bwisenosimenkg

I have attached the worked out qvw as well. Hope this helps.

Tags (2)
Labels (1)
Attachments
Comments

Super. Thanks for this tresesco

0 Likes
krishna20
Valued Contributor II

That's Great tresesco.

Thank's for super Document.

0 Likes

To give a quick context of why I was using Sum({1<Product=p(Product)>} Sales) was because I was under the impression that it worked the same way as Sum({1<Product=$::Product>} Sales)

and I got this information from the document where I learnt most of my set analysis -> Set Analysis: syntaxes, examples‌ (Page Number 18)

Capture.PNG

But thanks for pointing out the difference between the two

0 Likes
MVP & Luminary
MVP & Luminary

How this is related to Data Quality, the realm of this Group?

0 Likes
MVP
MVP

Thanks Ralf, I have finally been able to change the publishing path.

0 Likes
mkamal
New Contributor

What if you wanted to ignore all selections except for Region (current Selection)?  How would you write the set analysis?

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2015-10-16 07:53 AM
Updated by: