Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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
But what happens, if I select Year=2011 ? The value changes (see, below) which we never expected
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.
Super. Thanks for this tresesco
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=$::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)
But thanks for pointing out the difference between the two
How this is related to Data Quality, the realm of this Group?
Thanks Ralf, I have finally been able to change the publishing path.
What if you wanted to ignore all selections except for Region (current Selection)? How would you write the set analysis?
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.
Week | Order |
1 | |
2 | Order 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!