Set Analysis - consider selective filters

[#1]

     Hi all,

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

A	location	USA
A	Score	3
B	location	USA
B	Score	4
C	location	Europe
C	Score	5
D	location	Europe
D	Score	6

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

[maleksafa]

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.

[#3]

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.

[#4]

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

[maleksafa]

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.

[#6]

@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?

[jonathandienst]

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.

[jonathandienst · Accepted solution]

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.

[#9]

Thank you so much. This works great