Set Analysis: Ignore a large group of fields

Anonymous — Sat, 16 Nov 2024 10:59:12 GMT

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.

Re: Set Analysis: Ignore a large group of fields

maxgro — Fri, 16 Oct 2015 19:09:25 GMT

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)

topic Re: Set Analysis: Ignore a large group of fields in App Development

Set Analysis: Ignore a large group of fields

Re: Set Analysis: Ignore a large group of fields