2 Replies Latest reply: Sep 22, 2014 7:57 AM by Valeriy Shylin RSS

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

    Valeriy Shylin

      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.