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: 
alenb
Partner - Contributor III
Partner - Contributor III

Filtering the dimension with condition

I have the following data, and I would like to display a simple table with all the Brands based on the Team to which a user belongs.

Brands:
LOAD * Inline [
Team, Brand
1, a
1, b
2, b
2, c
];

 

Users:
User, Team1, Team2
FRED, admin, user
WILMER, user

 

If I use the simple hardcoded example, it works. Only a, b Brands are shown in the table.

=if(Team=1, Brand)

But if I use a more complex condition, it is giving me "Invalid Dimension" error. Although condition by itself works OK in other expressions. 

=if(Len(only({<User={'FRED'}>}[Team1])) > 0, Brand)

Even the slightly modified version gives the same error. It looks like the problem might be in the "only" function, but I have no idea why or how to fix it.

=if(only({<User={'FRED'}>}[Team1]) = 'admin', Brand)

 

2 Solutions

Accepted Solutions
agigliotti
Partner - Champion
Partner - Champion

Hi @alenb ,

You should use an Aggr function as below:

=Aggr( if( only( {< User = {'FRED'} >} [Team1] ) = 'admin', Brand ), Brand )

for the calculated dimension plus untoggle "include null values".

anyway in your case i can't see the relation between the two tables.

I hope it can help.

Best Regards

View solution in original post

Chanty4u
MVP
MVP

or this one too

=if(aggr(Len(only({<User={'FRED'}>}[Team1])) > 0, Brand),Brand)

View solution in original post

4 Replies
agigliotti
Partner - Champion
Partner - Champion

Hi @alenb ,

You should use an Aggr function as below:

=Aggr( if( only( {< User = {'FRED'} >} [Team1] ) = 'admin', Brand ), Brand )

for the calculated dimension plus untoggle "include null values".

anyway in your case i can't see the relation between the two tables.

I hope it can help.

Best Regards

Chanty4u
MVP
MVP

can you try this

=if(aggr(only({<User={'FRED'}>}[Team1]) = 'admin', Brand),Brand)

Chanty4u
MVP
MVP

or this one too

=if(aggr(Len(only({<User={'FRED'}>}[Team1])) > 0, Brand),Brand)

alenb
Partner - Contributor III
Partner - Contributor III
Author

It doesn't give me error anymore, but looks like I've not set the correct condition. My intention was to only show the Brands from the teams of specific user. 

Basically WILMER should see only: b, c (only from Team2)

FRED should see: a, b, c (from Team1 and Team2)

I wanted to use the condition from the original question as the starting point but I see now this won't work, any idea how to achieve this?