I am trying to build a filter based on multiple case functions. I have different three score cards build on the following case functions
-- metric 1: Aptitude Test Completed
CASE WHEN total_score IS NOT NULL THEN 1 ELSE 0 END
-- metric 2: Aptitude Test Passed
CASE WHEN aptitude_test_date_attempted IS NOT NULL AND
literacy_score >= 5 AND
logic_score >= 6 AND
numeracy_score >= 6 AND
sequence_score >= 6 AND
statistics_score >= 5 AND
total_score >= 36 THEN 1 ELSE 0 END
-- metric 3: Applications Accepted
CASE WHEN aptitude_test_date_attempted IS NOT NULL AND
application_status = "Accepted" THEN 1 ELSE 0 END
So now i want to built a filter(drop-down list component) as well that will have those three sections in it and this was my case functions
CASE
WHEN aptitude_test_date_attempted IS NOT NULL AND
application_status = "Accepted" THEN "Applications Accepted"
WHEN aptitude_test_date_attempted IS NOT NULL AND
literacy_score >= 5 AND
logic_score >= 6 AND
numeracy_score >= 6 AND
sequence_score >= 6 AND
statistics_score >= 5 AND
total_score >= 36 THEN "Aptitude Test Passed"
WHEN total_score IS NOT NULL THEN "Aptitude Test Completed"
ELSE NULL
END
The most obvious issue is the cut in number of Aptitude Test Completed due to the total_score field being shared in two cases. the score card of individual case function has a larger number which is correct but the number shown in the filter is way less which is incorrect. Is there a workaround this?
User | Count |
---|---|
1 | |
1 | |
1 | |
1 | |
1 |