If(Missing = Missing, 1, 2) always yields Null

[Anonymous]

I noticed a difference in how different versions of Qlikview manage one specific case with calculated dimensions.

And QV 11.0 does it better than 11.2.

I have 3 tables: Fact, User, SpecialUserMark. My chart counts unique identifiers in Fact. But the dimension is based on Users.

In particular, I want to count how many records in Fact belong to Normal and Special users.

So, the calculated dimension is the following:

IF(User.UserValue = SpecialUser.SpecialValue,'Special User', 'Normal User')

The expression is the following:

count(DISTINCT FactTable.DistinctID)

My problem is that some Fact table rows don't have the corresponding user. So, the dimension formula translates more or less in IF(Missing = Missing, 'something', 'something else').

Below is how QV 11.0 (11.00.11282.0 SR1 64-bit Edition) displays the table:

Naturally, the missing users (UserId = k) are treated as "something else" => "Normal User".

Below is how QV 11.2 (11.20.11922.0 SR2 64-bit Edition) displays the same table:

The missing users are treated as NULL (I tried changing the NULL symbol representation). The whole formula gets invalidated. So, wrapping it inside IF(ISNULL(IF(missing=missing,...,...)),...,...) does not work as it keeps always yielding NULL.

Find attached the example.

Any ideas on how to solve this problem?

The only solution I came up with is to create "fake" records in the User table, which makes it enough for the formula to get evaluated correctly.

But I have too many similar problems in the same dashboard and these "fake" records eventually affect calculations in other charts.

[anbu1984 · Accepted solution]

Try this

=IF(IsNull(%UserID) Or IsNull(SpecialUser.SpecialValue) Or Len(Trim(User.UserValue)) = 0, 'Normal User' ,If(User.UserValue = SpecialUser.SpecialValue,'Special User'))

[Anonymous]

It seems to me that this expression is enough:

IF(

 not(IsNull(User.UserValue) or IsNull(%UserID)) and User.UserValue = SpecialUser.SpecialValue, 

 'Special User',

 'Normal User'

)