1 of 1 people found this helpful
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
ELSE ZN([Q1]) + ZN([Q2]) + ZN([Q3]) + ZN([Q4]) + ZN([Q5])
(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!
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!