2 Replies Latest reply on Dec 3, 2018 5:32 PM by Christine___

    How can I calculate psychometric scores accounting for missing values in tableau prep

    Christine___

      I am computing a range of scores based on psychometric tests (for example the Kessler 10 and the Personal Wellbeing Index). This is a straightforward task in SPSS/Stata/SAS etc but I am trying to replicate this in Tableau Prep.

      One helpful variable would be a count of null values across the variable set, or a count of the variables with x value in the variable set. Is this possible?

       

      Rules include variations on the following:

      • Sum the scores unless there are two or more items missing in which case exclude
      • Sum the scores unless every score is 5
      • if only 1 item is missing, create a pro-rata score which accounts for 4 of the 5 scores (inflate by 5/4 = 1.25)

       

      Other tools may get you to create an average score of non-missing values if less than n values are missing.

       

      My data are similar to this, however depending on the scale they might have a larger range or slight variations on the rules for calculation:

       

      RowQ1Q2Q3Q4Q5Score should be
      13435318
      2null435521
      35455423
      4null443null
      555555null
      6nullnullnullnullnullnull

       

      Other considerations are that I have multiple observations per client to account for.

        • 1. Re: How can I calculate psychometric scores accounting for missing values in tableau prep
          Joshua Milligan

          Hi Christine,

           

          I would probably tackle this with a series of calculations.  One would be Number of NULLs:

           

          IIF(ISNULL([Q1]), 1, 0)

          +

          IIF(ISNULL([Q2]), 1, 0)

          +

          IIF(ISNULL([Q3]), 1, 0)

          +

          IIF(ISNULL([Q4]), 1, 0)

          +

          IIF(ISNULL([Q5]), 1, 0)

           

           

                 You might have to adjust if the value could be blank instead of just NULL -- so for example, you might have to replace IIF(ISNULL([Q1]), 1, 0) in the code above with IF LEN(TRIM(IFNULL([Q1], '')) == 0 THEN 1 ELSE 0 END

           

          The next would be the Number of 5s

           

          IIF([Q1] == 5, 1, 0)

          +

          IIF([Q2] == 5, 1, 0)

          +

          IIF([Q3] == 5, 1, 0)

          +

          IIF([Q4] == 5, 1, 0)

          +

          IIF([Q5] == 5, 1, 0)

           

           

          Then, based on those two, you'll have the foundation for some logic for a final calculation - Score

           

          (

          IF [Number of NULLs] >= 2 OR [Number of 5s] == 5 

          THEN NULL

          ELSE ZN([Q1]) + ZN([Q2]) + ZN([Q3]) + ZN([Q4]) + ZN([Q5])

          END

          )

           

          /

           

          (IF [Number of NULLs] == 0 THEN 5 ELSE 4 END)

           

           

           

           

          The first part (the numerator) makes sure you have enough values and not all are 5 and if so, it adds everything together (using the ZN() function to replace NULLs with 0 so the entire thing doesn't end up NULL).

          The second part (the denominator) determines whether to divide by 4 or 5 based on if you have a missing value or not.

           

          Hope that helps!

          Joshua

          1 of 1 people found this helpful
          • 2. Re: How can I calculate psychometric scores accounting for missing values in tableau prep
            Christine___

            Many thanks, this is exactly the code I was looking for. I did not know what IIF and ZN were used for so this is super helpful and extremely obvious now that I realise they exist!