Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
camron_allan
New Contributor II

Set Analysis: Ignore a large group of fields

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.

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Set Analysis: Ignore a large group of fields

I think this for ignoring all fields of a table

for detail, see here

Ignore all selections except some specific fields using Set Analysis

count({$<[$(=Concat({1<$Table={ProductTable}>}distinct $Field,']=,[')&']=')>} distinct location_sk)

View solution in original post

1 Reply
Highlighted
MVP
MVP

Re: Set Analysis: Ignore a large group of fields

I think this for ignoring all fields of a table

for detail, see here

Ignore all selections except some specific fields using Set Analysis

count({$<[$(=Concat({1<$Table={ProductTable}>}distinct $Field,']=,[')&']=')>} distinct location_sk)

View solution in original post