1 Reply Latest reply: Oct 16, 2015 3:09 PM by Massimo Grossi RSS

    Set Analysis: Ignore a large group of fields

    Camron Allan

      Is there a way to ignore a group of fields or tables in a set expression?  Here is a simplified example of what I am trying to do:

       

      Here's our fact table.


      Fact Table

      Field
      month_sk
      product_sk
      location_sk
      value

       

      It's a snowflake model.  Each of the surrogate keys leads to a dimension table that fans out to a number of related tables.  The user will select one month and then filter on various region and product attributes.

       

      We want to count the number of locations, regardless of the products selected.  I would normally use the following expression to ignore the product selection, while still honoring the month and location context:

       

      COUNT( {$<product_sk=>} DISTINCT location_sk)

       

      However, the user may make a selection on any of the product-related attributes.  If I add set modifiers to explicitly ignore all the product attributes, the expression will get quite long.

       

      Is there a way to ignore a group of fields or whole tables?  I was hoping that I could use the Tag script function to tag all my product, time, and location fields, but I can’t see how the field tags can be used in a set expression.  I feel like I might be missing something fundamental.

       

      Alternatively, I know the list of fields I want to ignore.  I figure I can get them into a comma-separated list using the Concat function.  Is there a way to use set arithmetic and GetCurrentSelections to find only the field selections I wish to honor?


      {current selections} - {product-related attributes}

       

      ...not really sure how that one would go.

       

      I'm using Qlik Sense 2.1, but I think this applicable to QlikView as well.

       

      Thanks in advance.