Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

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=$:Smiley Tongueroduct>} 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

krishna20
Valued Contributor

That's Great tresesco.

Thank's for super Document.

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=$:Smiley Tongueroduct>} 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

MVP
MVP

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

MVP
MVP

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

mkamal
New Contributor

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

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