Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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)