7 Replies Latest reply: Apr 24, 2015 4:03 AM by Manish Kachhia

# Use field value in expression, without having it as dimension

Dear Community,

how can i use a field value in an expression, without having it as dimension in straight table??

• ###### Re: Use field value in expression, without having it as dimension

TOTAL Qualifier... + Aggr Function

• ###### Re: Use field value in expression, without having it as dimension

In detail i need to check if a variable of an column matches a set of Field values to change a color.

=if(match(mid(VALUE,1,6),total(FIELD_VALUES)),RGB(255,90,80))

total is not working here!?

• ###### Re: Use field value in expression, without having it as dimension

Need to understand where you have used this expression? What is the dimension and expression of chart?

• ###### Re: Use field value in expression, without having it as dimension

Straight table with dimension values A-C, one value C need to be highlighted if it matches a set of values of an dimension D that is not part of this table. So the expressions is inside Dimension C as background color expression.

• ###### Re: Use field value in expression, without having it as dimension

I think i am now really close to solve this problem:

to get the multiple row values in one i use

CONCAT(DISTINCT(chr(39)&FIELD_VALUES&chr(39)),', ') => As a result i get = '123, '234'

When i use this result directly inside if(match(mid(VALUE,1,6),'123, '234'),RGB(255,90,80)) = It works

But when i use the function above that produces this result it does not work!

if(match(mid(VALUE,1,6),(CONCAT(DISTINCT(chr(39)&FIELD_VALUES&chr(39)),', '))),RGB(255,90,80))

What i am making wrong????

• ###### Re: Use field value in expression, without having it as dimension

if(match(mid(VALUE,1,6),\$(=(CONCAT(DISTINCT(chr(39)&FIELD_VALUES&chr(39)),', ')))),RGB(255,90,80))

• ###### Re: Use field value in expression, without having it as dimension

Theoretically, there is nothing to stop you from doing that. Practically, you need to know what to expect, logically. I'll give you two examples:

1. Chart per Product, when Color is an attribute of a Product. In this case, something like this is perfectly fine, because there is always only one Color per Product:

IF(Color = 'Blue', ...)

2. The same expression in a chart per Customer would return a NULL value because there are multiple Colors per Customer. In this case, you'd have to use AGGR to force the calculation at a different level of detail. For example:

AGGR(

IF(Color = 'Blue', ...),

Customer, Color)

This calculate will be technically correct, because the Color will be unique within an AGGR that has the field Color as a Dimension.

cheers,

Oleg Troyansky

Come and learn AGGR and Set Analysis with me at the Masters Summit for QlikView!