
1. Re: How can I calculate psychometric scores accounting for missing values in tableau prep
Joshua Milligan Dec 3, 2018 8:05 AM (in response to Christine___)1 of 1 people found this helpfulHi 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

2. Re: How can I calculate psychometric scores accounting for missing values in tableau prep
Christine___ Dec 3, 2018 5:32 PM (in response to Joshua Milligan)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!