Dimension with conditional based on measures (master item)

[mendes094]

Hello, 

I need a help with a dimension master item, when I put the expression if(CITY>0,'FULL','NOT FULL') doesn't work, the CITY is a measure = sum(target). The results are just 'NOT FULL'.

Anybody can help me?

Tks.

[DWolsie]

Hi @mendes094 , would it be possible for you to provide some of the data, an example of a table for example?

Additionally, could you try replacing CITY in the dimension with sum(target)?

[mendes094]

The colunm DIMENSION returns just 'NOT FULL' but according to expression we should see colunm EXPECTED DIMENSION.

[Kushal_Chawda]

@mendes094  try below expression

=aggr(if( [CITY] >0,'FULL','NOT FULL'),Region,Target,Class)

[Vegar]

I notice that CITY is a measure. Assuming it is sum (CITY) in my sample data set below. That data set will give you the output (white parts) of you table image.

LOAD * inline[
CITY,   REGION,   TARGET,    CLASS 
7,   	BLACK,   10,   		 A   	
-1,   	BLACK,   10,   		 A   	
-15,   	YELLOW,  5,  		 B   	
10,   	YELLOW,  5,    		 B   	
10,   	GREEN,   7,  		 C   	
-9,   	GREEN,   7,  		 C   	
2,   	BLUE,    8,  		 D   	
4,   	BLUE,    8,  		 D   	
-1,   	BLUE,    8,   		 D   	
-8,   	WHITE,   11, 		 E   	
-8,   	WHITE,   11, 		 E   	
8,   	WHITE,   11,  		 E   	
];

Using an expression IF(CITY>0,'FULL','NOT FULL') will look at the CITY values and try to aggregate them on the REGION, TARGET and CLASS dimension. Since you have not provided an explicit aggregation function such as avg() or sum() then a behavior identical to only() will be used. Qlik will therefore interpret your expression as this: IF(only(CITY)>0,'FULL','NOT FULL')

Try replacing your expression to this: IF(sum(CITY)>0, 'FULL','NOT FULL')

Alternatively add it as a dimension, then you should look at @mendes094 suggestion , using the definition aggr(if( sum([CITY]) >0,'FULL','NOT FULL'),REGION,TARGET,CLASS)