I'm fairly sure this can be done....but...

[samvile18]

...I need some help achieving it!

I want to be able to create a list box with two selections, the two selections will be <250,000 FUM and <10 MEMBERS.

When the user selects one of these I want my QVW to only show the Fund Names that meet the selected criteria.

Is that possible?

Any help is greatly appreciated.

[#2]

Hello Sam,

For list box with conditions, create a list box with expression with simple ifs:

if(FUM <250,000 and MEMBERS<10 ,FundNames)

Regards,

Kiran.

[samvile18]

Hi,

I don't actually need a list box based on the criteria...I need the app itself to change based on the users selection.

e.g. A table box showing 50 Fund Names and the FUM...when a user selects <250,000 the Fund Names that have FUM of <250,000 are left and the others disappear.

Does that make sense?!

[#4]

Qlikviews native filters only apply for equal to condition. For this you need to create calculated dimensions like,

IF(FUM<250000,'<250000','>=250000'). Having this in a list box, if the first one is selected automatically the filter is applied on your data model.

Requirement is genuine but usually this is how I do. If you need the filter to apply for specific charts, you can use set analysis.

Regards,

Kiran.

[johnw]

I assume there is some overlap between these conditions.  Basically, you have FOUR different cases:

1) <250,000 FUM & < 10 MEMBERS
2) >=250,000 FUM & < 10 MEMBERS
3) <250,000 FUM & >= 10 MEMBERS
4) >=250,000 FUM & >= 10 MEMBERS

And I believe you want this behavior:

If "<250,000 FUM" is selected, you want cases 1 and 3.
If "<10 MEMBERS" is selected, you want cases 1 and 2.
If both are selected, you want cases 1, 2 and 3.
If neither is selected, you want cases 1, 2, 3 and 4.

Is that right?  If so, see the attached example.  Here's the script:

Data:
LOAD recno() as Case, * INLINE [
FUM, MEMBERS
200000, 5
300000, 5
200000, 15
300000, 15
];

NewTable:              LOAD DISTINCT Case,'<250,000 FUM' as NewField RESIDENT Data WHERE FUM <250000;
CONCATENATE (NewTable) LOAD DISTINCT Case,'<10 MEMBERS'  as NewField RESIDENT Data WHERE MEMBERS <10;

[samvile18]

Hi John,

You're right with your line of thinking...can you help me with the next steps please?

In my QVW I have the following 2 tables:

[FUM by Fund]:
LOAD [Fund Name],
     [Fund No],
     [Fund Value],
     Year
FROM

(qvd);

[Members by Fund]:
LOAD MemberExtractDate,
     MemberFundName,
     MemberFundName & '-' & MemberExtractDate as Key,
     MemberFundNo,
     TotalMembers,
     MemberPlatform
FROM

(qvd);

I think what needs to happen is to apply your theory but with these two tables instead of the inline table. The FUM field should link to the Fund Value and the MEMBERS to the TotalMembers.

Is that possible?

[johnw · Accepted solution]

These two tables appear to have no relationship to each other.  Are they supposed to link by fund number?  If so, probably do something like this.

[FUM by Fund]:
LOAD [Fund Name],
     [Fund No],
     [Fund Value],
     Year
FROM

(qvd);

[Members by Fund]:
LOAD MemberExtractDate,
     MemberFundName,
     MemberFundName & '-' & MemberExtractDate as Key,
     MemberFundNo as [Fund No],
     TotalMembers,
     MemberPlatform
FROM

(qvd);

NewTable:             
LOAD DISTINCT [Fund No],'<250,000 FUM' as NewField
RESIDENT [FUM by Fund] WHERE [Fund Value] <250000;
CONCATENATE (NewTable)
LOAD DISTINCT [Fund No],'<10 MEMBERS' as NewField
RESIDENT [Members by Fund] WHERE TotalMembers <10;

[samvile18]

Once again thanks for your help John...I swear you're some sort of Qlikview Jedi!!