Get Distinct Count of Items in between two fractiles

[michael_andrews]

I'm trying to find the count of items in specific quintiles, but my expression keeps saying I'm wrong. Can someone tell me what is wrong with this?

Count(DISTINCT IF(ExamScore > Fractile(ExamScore,0.20) AND ExamScore <= Fractile(ExamScore,0.40), ExamID, 0))

Basically, I want a distinct count of ExamID where the ExamScore was higher the the 20th fractile, but lower or equal to the 40th fractile as well.

[Anil_Babu_Samineni · Accepted solution]

Try like below

Count({<ExamScore = {"> $(=Fractile(ExamScore,0.20)) <= $(=Fractile(ExamScore,0.40))"}>} DISTINCT ExamID)

[michael_andrews]

Maybe i'm doing something wrong, but that gives me all 0s (see image)

Basically, how that data works is this is data from tests that people took. Each test has multiple questions associated to them. The very far left is a question from the test, then the next column is all available answers for that question. What I want to do is display the count for each test score (ExamScore) where that individual test selected that response on that question

Sample sql would look something like this

DECLARE @20Frac = 16 --assume that this would be the value of Fractile(ExamScore, .20)
DECLARE @40Frac = 34 --assume that this would be the value of Fractile(ExamScore, .40)


--this would give me a count of exams between the 20th and 40th fractile, where that exam had selected a specific response
SELECT COUNT(ExamID)
  FROM Exam
       INNER JOIN ExamItem --the questions
            ON ExamItem.ExamID = Exam.ExamID
       INNER JOIN ExamItemResponse --the selected answer for said question
            ON ExamItemResponse.ExamItemID = ExamItem.ExamItemID
                AND ExamItemResponse.ExamID = Exam.ExamID
  WHERE ExamItemResponse.ExamItemID = @curID --the current response id, the second column in my chart
       AND ExamScore >@20Frac AND ExamScore <= @40Frac

[michael_andrews]

Actually, your answer was correct, I had the wrong field.

[Anil_Babu_Samineni]

I don't think whether you are doing wrong or not

But, This is simple DB Queries. Instead of that do in Qlikview. And one more thing DB Queries always won't work as in script. So that case you may translate to Qlikview

Good, Some what you got answer