Skip to main content
Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

Ignore all selections except few fields using Set Analysis

cancel
Showing results for 
Search instead for 
Did you mean: 
tresesco
MVP
MVP

Ignore all selections except few fields using Set Analysis

Last Update:

Sep 20, 2022 1:59:53 PM

Updated By:

Sue_Macaluso

Created date:

Oct 16, 2015 7:53:05 AM

Attachments

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.

Labels (1)
Comments
sunny_talwar

Super. Thanks for this tresesco

0 Likes
krishna20
Specialist II
Specialist II

That's Great tresesco.

Thank's for super Document.

0 Likes
sunny_talwar

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
rbecher
MVP
MVP

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

0 Likes
tresesco
MVP
MVP

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

0 Likes
mkamal
Contributor III
Contributor III

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

0 Likes
jjbom1990
Creator
Creator

I know this is a rather old question but hopefully still relevant. I have tried this solution but it doesn't seem to work for my situation. Probably because I am trying to count the weeks within a date selection. However de data is obviously linked to the facts. 

The expression to count weeks: 

 

count({1<Year=$::Year,Month=$::Month,Quarter=$::Quarter>}distinct YearWeek)

 

This works fine without being put into a table with dimensions. In a text box it nicely displays 5 weeks voor August 2021. 

However putting this into a table where there is only 1 order in 1 week it counts the weeks as 1 instead of 5. 

WeekOrder
1 
2Order no.
3 
4 
5 

 

The avg deliveries per week --> 1 / count({1<Year=$::Year,Month=$::Month,Quarter=$::Quarter>}distinct YearWeek) = 1 instead of the expected 0.2. how to overcome this?

Paging @tresesco  and @sunny_talwar 

 

Thanks in advance!

Version history
Last update:
‎2022-09-20 01:59 PM
Updated by: