If conditional statement with AND function

[Coloful_Black]

Hi community,

I am trying to create a column at UI's table to list a product type (A, B ,C or D) by using if statement.

my formula at dimension is this :

if ((AVG PRICE) > 10 and (MKT%) > 0.25, "A",
if ((AVG PRICE) > 10 and (MKT%) < 0.25, "B",
if (AVG PRICE) < 10 and (MKT%) > 0.25, "C","D")))

AVG PRICE and MKT% are created measures to help eliminate formula clutter. 

is there anything I miss so my formula does not work?

[Lisa_P]

Use the Expression Editor to create your expression and check if the syntax is correct in the bottom left.

Typically, when you use a master item in an expression, it will be contained in [Square brackets] and your product types should be in single quotes, so try this ..

if ([AVG PRICE] > 10 and [MKT%] > 0.25, 'A',
if ([AVG PRICE] > 10 and [MKT%] < 0.25, 'B',
if ([AVG PRICE] < 10 and [MKT%] > 0.25, 'C','D')))

[Coloful_Black]

Hi Lisa,

Thanks for the tips on syntax.

however, I tried your way and still does not get any good result. 

Is the set expression/formula supposed to go to dimension or measurement ?

I tried to simplify my expression to tease out what I am missing.

so I created this 

Again, the GP is self created master measures. when I input this, the expression edit shows error like this

  

but the output seems good like this ...

now I am totally lost. Any clue?

[Lisa_P]

This will also depend on the version you are using. Some of these features are new. If you have another column in the same table as a measure, you can use the title within the same table and it understands.

[Coloful_Black]

but why my simple if statement ends up with expression error?

I tried to input in both measurement and dimension. all the same.... so confused.

[coloful_architect · Accepted solution]

Try to use variable rather than master measure.

I had same issue. Master measure at my Qlik sense version is ok for a simple measurement replacement with color scheme. but when try to insert it with complex set expression formula, it does not necessarily always work.