Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mikelutomski
New Contributor III

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
MVP
MVP

Re: Opposite Associations

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)

7 Replies
mikelutomski
New Contributor III

Re: Opposite Associations

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.)

Employee
Employee

Re: Opposite Associations

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

Mike

Regards,
Mike Tarallo
Qlik
Employee
Employee

Re: Opposite Associations

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
MVP
MVP

Re: Opposite Associations

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
New Contributor III

Re: Opposite Associations

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.

Employee
Employee

Re: Opposite Associations

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
New Contributor III

Re: Opposite Associations

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.

Community Browser