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

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

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

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!