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.