One expression shows different result as a Dimension and a Measure

[lu156478864]

Hi all,

I have expression:

=$(vSupplier)=1 and
GetPossibleCount(CaseID)=1 and
GetPossibleCount(Label)=1 and
GetPossibleCount(Year)=1
or
if(match(Label, 'Hours') and isnull(subfield([$keyHours], '||',2)),1,
if(not match(Label, 'Hours') and isnull(subfield([$keyAll], '||',2)),1,
0))

vSupplier is variable for button. What is strange: If I use this expression in table as a Dimension it shows great result: 0, If I use it as a Measure it shows wrong result: -1. I am using it in the same table (I need it for show/hide condition) in chart.

Don't you know where can be problem and how to solve it? 🤔

[henrikalmen]

Your expression is very confusing to me. Simplified (and I removed some of the conditions for simplicity) you could end up with for example:

1=1 and 0=1 or 0

What does that even do? 

You should also group your conditions with approporiate parenthesis. And I think you should wrap the whole thing in an if-statement, that will make it easier to read (and perhaps easier for you to see where you went wrong).

It's not so much a matter of dimension (which this should probably not be) or measure. It's a matter of making sure the expression does exactly what you want it to do, and I would think it doesn't serve your purpose right now.

[lu156478864]

Thank you so much for your reply.

Problem is in this part: 

if(match(Label, 'Hours') and isnull(subfield([$keyHours], '||',2)),1,0).

Qlik evaluates the condition separately if(match(Label, 'Hours') and isnull(subfield([$keyHours], '||',2)),1,0). So I have 2 results 1 and 0 in dimensional view because I filtered value "Hours" (1) and "keyHours" is not null (0). 

For this case I want to evaluate this expression as one - so If those 2 parts are true->1, if one of them or both are false->0.

Don't you know how to edit this expression?

[marcus_sommer · Accepted solution]

isnull() will return TRUE if the result is a real NULL. If you want to consider not NULL you need to add it to the condition, like: ... NOT isnull() ...

Beside this it might be more suitable not to query to isnull() else against len(trim()) and/or to combine it with coalesce().

[henrikalmen · Accepted solution]

Create a text object with two different expressions: 
=if(match(Label, 'Hours')
=isnull(subfield([$keyHours], '||',2))

This way you can see what they actually evaluate to in different situations. Both should become either 0 (False) or -1 (True).

You can also test these different evaluation scenarios like this:

=if(-1 and -1, 1, 0)
=if(0 and -1, 1, 0)
...and so on.

This way you should be able to find out why your expression doesn't behave like you expect it to.

[lu156478864 · Accepted solution]

Thank you it works. I evaluate all parts of if function and replaced isnull() function. Original: if(match(Label, 'Hours') and isnull(subfield([$keyHours], '||',2)),1,0) -> What worked: if(match(Label, 'Hours')=1 and count(subfield( [$keyHours], '||',2))<1,1,0)