Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
therealdees
Creator III
Creator III

Secondary filter to exclude values from measures

Hi,

I have a table for customer behavior analysis that the user can filter clothing brands and see data only for those who have purchased such brand, e.g NIKE.

What I want to do is add an additional filter for exclusion: if the user has selected NIKE in the primary filter and ADIDAS in the secondary filter, it should show only data for customers that have bought NIKE but haven't bought ADIDAS.

I've found some posts about the subject but I couldn't make it work. The closest I got was using alternate states to the exclusion filter and pointing out the state in the set analysis.

What is the best way to achieve this?

Labels (2)
3 Solutions

Accepted Solutions
Clement15
Creator III
Creator III

Clement15_0-1712215494021.png

For the second input variable you just have to choose another variable here vTest2.

In the values the &'| ' after the Concat allows you to add an empty value allowing you to not have a filter

 

View solution in original post

therealdees
Creator III
Creator III
Author

Hi, @Clement15 

Thanks for the reply!

 

I'm trying to understand your solution, but I'm a bit confused, could you explain me some more?

 

1. Do I have to work with variable inputs in both cases? Or could I use a filter in the first case (or maybe in both)? I ask this, because the user might want to filter more than a brand (e.g customers that have bought Nike or Lacoste) and exclude another (possibly more than one brand as well, e.g: that haven't bought Adidas and CK)

2. If I'm understanding it right, you're multiplying the P() by the E() in the expression, if that's right, why?

3. What did you add to the variable? Is it a blank/null value and the values are then fulfilled with the Concat(Distinct Brand, '|')&'|'? If so, is this the complete expression in the dynamic values or is there more that is not being show in the picture?

 

View solution in original post

therealdees
Creator III
Creator III
Author

You gave me an insight using concat() and I managed to achieve what I wanted with a different approach. GetFieldSelections() will also concatenate the values and I can use it inside the set analysis

 

For those who it might help, I used alternate states and getfieldselections:

Filter 1 = product_brand (inherited state, no need for changes)

Filter 2 (exclusion) = I created an alias field (product_brand_exc) and added a different state (e.g BrandExclusion) [maybe it's not necessary and you could use the same product_brand as it's using a different state]

Then in the measure expression you could use GetFieldSelections() to retrieve the selected values in Filter 2:

 

Count(Distinct {<key_customer = E({BrandExclusion<product_brand = {$(=chr(39)&GetFieldSelections(product_brand_exc,''',''',10,'BrandExclusion')&chr(39))}>} key_customer)>} sale_id)

 

Note that for this to work, you must use chr(39) (single quote) outside the GetFieldSelections and not use double quotes in the {} set analysis, plus single quotes before and after the delimiter (as single quote would actually comment the code, you must use the escape method to identify the single quote as a string instead of a "function": ''','''

 

@Clement15  thanks for your help

View solution in original post

3 Replies
Clement15
Creator III
Creator III

Clement15_0-1712215494021.png

For the second input variable you just have to choose another variable here vTest2.

In the values the &'| ' after the Concat allows you to add an empty value allowing you to not have a filter

 

therealdees
Creator III
Creator III
Author

Hi, @Clement15 

Thanks for the reply!

 

I'm trying to understand your solution, but I'm a bit confused, could you explain me some more?

 

1. Do I have to work with variable inputs in both cases? Or could I use a filter in the first case (or maybe in both)? I ask this, because the user might want to filter more than a brand (e.g customers that have bought Nike or Lacoste) and exclude another (possibly more than one brand as well, e.g: that haven't bought Adidas and CK)

2. If I'm understanding it right, you're multiplying the P() by the E() in the expression, if that's right, why?

3. What did you add to the variable? Is it a blank/null value and the values are then fulfilled with the Concat(Distinct Brand, '|')&'|'? If so, is this the complete expression in the dynamic values or is there more that is not being show in the picture?

 

therealdees
Creator III
Creator III
Author

You gave me an insight using concat() and I managed to achieve what I wanted with a different approach. GetFieldSelections() will also concatenate the values and I can use it inside the set analysis

 

For those who it might help, I used alternate states and getfieldselections:

Filter 1 = product_brand (inherited state, no need for changes)

Filter 2 (exclusion) = I created an alias field (product_brand_exc) and added a different state (e.g BrandExclusion) [maybe it's not necessary and you could use the same product_brand as it's using a different state]

Then in the measure expression you could use GetFieldSelections() to retrieve the selected values in Filter 2:

 

Count(Distinct {<key_customer = E({BrandExclusion<product_brand = {$(=chr(39)&GetFieldSelections(product_brand_exc,''',''',10,'BrandExclusion')&chr(39))}>} key_customer)>} sale_id)

 

Note that for this to work, you must use chr(39) (single quote) outside the GetFieldSelections and not use double quotes in the {} set analysis, plus single quotes before and after the delimiter (as single quote would actually comment the code, you must use the escape method to identify the single quote as a string instead of a "function": ''','''

 

@Clement15  thanks for your help