Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
JulieMcD240
Contributor II
Contributor II

Dimensions with multiple criteria in Qlik Sense Enterprise

Hello experts! 

I created a dimension that has multiple criteria to only return specific employment codes and only in the years 2018 - 2022.  When I type this expression in the expression builder, it says that it is 'Ok' but when I add it as a dimension in a table, it says 'invalid dimension'.  Is there something wrong with this expression?

 only({<[employment]={'A', 'B', 'C', 'D', 'E', 'F'},[Year]={'2018','2019','2020','2021','2022'}>} [employment])

Thank you for your help.

Labels (4)
3 Solutions

Accepted Solutions
LucasBarbosa
Partner - Contributor II
Partner - Contributor II

Hello @JulieMcD240,

Only is an aggregation function and should be used as a measure in the table.

if you want to use it as a dimension I suggest using the aggr function

for example in the following data table:

 

ProductData:

LOAD * inline [

Customer|Product|UnitSales|UnitPrice

Astrida|AA|4|16

Astrida|AA|10|15

Astrida|BB|9|9

Betacab|BB|5|10

Betacab|CC|2|20

Betacab|DD||25

Canutility|AA|8|15

Canutility|CC||19

] (delimiter is '|');

 

As a measure:

 

=Only({<UnitPrice = {15}>} Product)

 

LucasBarbosa_1-1655212542668.png

As a dimension:

 

=Aggr(Only({<UnitPrice = {15}>} Product),Customer,Product,UnitPrice)

 

LucasBarbosa_0-1655212944888.png

reading references:

1. Only;

2. Aggr;

3. Aggregation functions;

 

View solution in original post

Filippo_Nicolussi_P

Hello @JulieMcD240 

 Only() returns "only" one value based on the help , what about switching to the aggr() that returns an array of values ( sounds more a Dimension)   https://help.qlik.com/en-US/sense/May2021/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/aggr.htm ? 

 

Help users find answers! Don't forget to mark a solution that worked for you! If already marked, give it a thumbs up!

View solution in original post

vinieme12
Champion III
Champion III

The calculated dimensions need to be wrapped in Aggr()

 

 

=Aggr(only({<[employment]={'A', 'B', 'C', 'D', 'E', 'F'},[Year]={'2018','2019','2020','2021','2022'}>} [employment]), [employment])

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

4 Replies
LucasBarbosa
Partner - Contributor II
Partner - Contributor II

Hello @JulieMcD240,

Only is an aggregation function and should be used as a measure in the table.

if you want to use it as a dimension I suggest using the aggr function

for example in the following data table:

 

ProductData:

LOAD * inline [

Customer|Product|UnitSales|UnitPrice

Astrida|AA|4|16

Astrida|AA|10|15

Astrida|BB|9|9

Betacab|BB|5|10

Betacab|CC|2|20

Betacab|DD||25

Canutility|AA|8|15

Canutility|CC||19

] (delimiter is '|');

 

As a measure:

 

=Only({<UnitPrice = {15}>} Product)

 

LucasBarbosa_1-1655212542668.png

As a dimension:

 

=Aggr(Only({<UnitPrice = {15}>} Product),Customer,Product,UnitPrice)

 

LucasBarbosa_0-1655212944888.png

reading references:

1. Only;

2. Aggr;

3. Aggregation functions;

 

Filippo_Nicolussi_P

Hello @JulieMcD240 

 Only() returns "only" one value based on the help , what about switching to the aggr() that returns an array of values ( sounds more a Dimension)   https://help.qlik.com/en-US/sense/May2021/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/aggr.htm ? 

 

Help users find answers! Don't forget to mark a solution that worked for you! If already marked, give it a thumbs up!
vinieme12
Champion III
Champion III

The calculated dimensions need to be wrapped in Aggr()

 

 

=Aggr(only({<[employment]={'A', 'B', 'C', 'D', 'E', 'F'},[Year]={'2018','2019','2020','2021','2022'}>} [employment]), [employment])

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
JulieMcD240
Contributor II
Contributor II
Author

Thank you!  That worked.

 

Julie