Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Set Analysis - consider selective filters

     Hi all,

I have a requirement where I need to compare one company's score against the rest. The data looks as shown below

AlocationUSA
AScore3
BlocationUSA
BScore4
ClocationEurope
CScore5
DlocationEurope
DScore6

User selects a company then he will see his company's score compared or bench marked against rest

I also want to give the user to benchmark a company's score against location. But in this case, when I choose company A, Europe is grayed out. But I need the user to choose his company and also benchmark it with whatever location he chooses (say, how is my company's score against all european companies)

How can I give user the option to select a company first, then choose the location ? Example app is attached with the post

Any help will be appreciated

Capture.PNG

1 Solution

Accepted Solutions
MVP
MVP

Re: Set Analysis - consider selective filters

Now to compare the selected company with a selection that might conflict (eg compare company A with location Europe), you need to use Alternate states. As an example, add a state 'Compare' to the qvw, create a another list box for Location and assign it to the state 'Compare', and then use this expression to get the benchmark average:

     Avg({Compare} Score)

Search this site for more information on using alternate states.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

8 Replies
maleksafa
Valued Contributor

Re: Set Analysis - consider selective filters

then you need to have the locations that you want to compare with in an island table or in the fact table but under different flag which is not linked to the company. This way you will be able to select a company and the locations will not be grey and there are no link between them and in the set analysis you will work on the comparison.

Not applicable

Re: Set Analysis - consider selective filters

Can you please let me know how I can achieve the same? or perhaps point me to an example to  create an island table or fact table with a flag? I am new to data modeling here.

Not applicable

Re: Set Analysis - consider selective filters

Any help guys? I tried to modify the data model but unable to.

maleksafa
Valued Contributor

Re: Set Analysis - consider selective filters

first of all i think you need to review your data model.

there are attributes on different lines that can be on the same line.

for example in your data model you are loading the data as follow:

   Company,Question, Answer

    A, Score, 3

    A, location, USA

however this is the same company but you are distributing the line and score on two different lines. what i would do is either to have them on the same line like the table will be Company, Location, Question, Answer or you will have this table having Company, Question, Answer and link it to another table in your model where you will have Company and Location fields.

let's start with that first and then we can move forward, with this data model it will be too complex to achieve what you want.

Not applicable

Re: Set Analysis - consider selective filters

@maleka Thanks for your reply. The results are from a survey and I cannot change the underlying model. I need to work with this model and transform this table structure. Any ideas how I can start?

MVP
MVP

Re: Set Analysis - consider selective filters

I assume you mean that you cannot change the source data. This will transform the source data:

Source:

LOAD Company, Question, Answer

From Source.txt (.....);

Results:

LOAD Company, Answer As Location

Resident Source

Where Question = 'location';

Join (Results)

LOAD Company, Answer As Score

Resident Source

Where Question = 'Score';

DROP Table Source;

Now you have a table with the fields Company, Location and Score.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: Set Analysis - consider selective filters

Now to compare the selected company with a selection that might conflict (eg compare company A with location Europe), you need to use Alternate states. As an example, add a state 'Compare' to the qvw, create a another list box for Location and assign it to the state 'Compare', and then use this expression to get the benchmark average:

     Avg({Compare} Score)

Search this site for more information on using alternate states.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

Not applicable

Re: Set Analysis - consider selective filters

Thank you so much. This works great