Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mikelutomski
Creator
Creator

Opposite Associations

Hello all. 

I'm new to Qlik (as of Sept '15) and I've finally hit my first real road block.

I have a Products table and an Accounts table.

I am trying to use 3 different Filters in my results.

Filter 1 is Product Line

Filter 2 is Product Line

Filter 3 is Account Name.

If I make a selection in Filter 3, I want Filter 1 to have the corresponding Products and Filter 2 to show what Products that they currently DO NOT HAVE. 

I'm basically trying to do the opposite of what the filter panes are set up to do.  Instead of Highlighting the ones that are linked, I want to highlight the ones that are usually greyed out.

Should I make a table that concatenates all products to the Account names, or is there a formula that I can use in the presentation layer that can make this happen?

Thanks in advance.

-Mike

1 Solution

Accepted Solutions
swuehl
MVP
MVP

I agree that this sounds like you just want to look at the greyed out values.

If you want to list only these values, you can maybe use a calculated dimension with aggr() function and set analysis, something like

=Aggr(Only({<Product =e(), Company=>}Product),Product)

View solution in original post

7 Replies
mikelutomski
Creator
Creator
Author

A little more clarification:

If I make a Product selection in Filter 1, I already know that Filter 3 will be limited to the Account Names that have that Product.

However, I want Filter 2 to be dynamic enough to know ALL Products that those Account Names have and filter it's results accordingly.

If you select Product A in Filter 1 and Filter 3 shows Company B.  However Company B also has Products D, G & K.

I would like Filter 2 to show the remaining Products that are available for Company B (Products B, C, E, F, H, I, J, etc.)

Michael_Tarallo
Employee
Employee

Hello Mike, I am pushing this to another section to get another set of eyes on it.

Mike

Regards,
Mike Tarallo
Qlik
Michael_Tarallo
Employee
Employee

Hi Mike,

Unless I am misreading this, this is how Qlik's associative model works by default.

Green is selected, White is what is associated with the selected, Dark grey is what is not related or associated and Light grey is what is also possible or available between the selections. 

If you have a moment, review these short videos / resources and let me know if I am on track. If not, please provide some screenshots:

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Regards,

Mike Tarallo

Qlik

Regards,
Mike Tarallo
Qlik
swuehl
MVP
MVP

I agree that this sounds like you just want to look at the greyed out values.

If you want to list only these values, you can maybe use a calculated dimension with aggr() function and set analysis, something like

=Aggr(Only({<Product =e(), Company=>}Product),Product)

mikelutomski
Creator
Creator
Author

Thanks for getting this in the right place, Michael.

Your response was my initial thought too.

I know it's redundant to have 1 column showing the Products the customer has, and another column that shows what they don't have, when the first column has all of the relevant information we need.

I'm going to try and sell your point a little harder.

Thanks again for your help.

Michael_Tarallo
Employee
Employee

Cool - check out Stefan's example too - I think it shows what you may want as well. Set Analysis, Aggr() and other functions can really create powerful expressions to do almost anything you want.

Take Care.

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Regards,

Mike Tarallo

Qlik

Regards,
Mike Tarallo
Qlik
mikelutomski
Creator
Creator
Author

Thank you swuehl

I knew it had to do something with the aggr() function, but couldn't piece it together properly.

As I said to Michael, I know it's redundant, but it's what the customer wants.

Thanks again.