Dimensions with multiple criteria in Qlik Sense Enterprise

[JulieMcD240]

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.

[LucasBarbosa · Accepted solution]

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)

As a dimension:

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

reading references:

1. Only;

2. Aggr;

3. Aggregation functions;

[Filippo_Nicolussi_P · Accepted solution]

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 ? 

[vinieme12 · Accepted solution]

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])

[JulieMcD240]

Thank you!  That worked.

Julie