Exams module extensions [2022:HPR]

[Ghislain Hachey]

The following shall be added to the current exams module

• Add support for Test Analysis (i.e. KR20, Cronbach’s Alpha, Distractor analysis, etc.). At a minimum it should support all the ones in the Test Analysis sheet of the AllSchools workbook of the legacy SOE tool. Additional relevant features can be added also.
• Support for an Exams tab in the Student profile page. Currently only matching students would have their exams data under their profile. The process of entering correct student names and ID needs to be improved for this to improved. Perhaps access to the Candidates students in the UI to “fix” the data there might be a worthy data improvement tool to consider
• Support for yearly trends on results where data is available for the same subject/grade.
• New exams analysis (i.e. by teacher, student vs sped thus we’ll need to load teachers, sped boolean at the least)
• Any other missing SOE feature
• Consider creating teachers and students when there are no matches to benefits from the dedupers
• Support for a custom item 61 answer in high school exam. Should be able to take a score 0 to 4 instead of a multiple choice A-D. Then this is used “list of high school passing student” report.

‌

[Ghislain Hachey]

Issue #1189 was marked as a duplicate of this issue.

[Brian Lewis]

@Ghislain Hachey None of the exam workbooks I have for testing have a “Test Analysis” page - can you send me one through please.

[Brian Lewis]

The following is some sketches that calculate Cronbach Alpha using the ExamCandidateItems data.

Also on the way to that is an analisys of each question, how many people answered each of the possible answers, % correct.

--select top 1000  *
--from ExamCandidateItems I
--  INNER JOIN ExamCandidates C
--      ON I.excID = C.excID
--WHERE exID = 32

declare @ID int = 32

select DISTINCT substring(exItemCode, 10, 8), exITemCode
from ExamCandidateItems I
    INNER JOIN ExamCandidates C
        ON I.excID = C.excID
WHERE exID = @ID
order by substring(exItemCode, 10, 8)

-- get a column of data for EXcel
--select exciResult 
--from ExamCandidateItems I
--  INNER JOIN ExamCandidates C
--      ON I.excID = C.excID
--WHERE exID = 32
--ANd exItemCode = 'ITEM_004_MS0401010104M_CCC'



--select exItemCode
--, case exciResponse when 'A' then 1 end A
--, case exciResponse when 'B' then 1 end B
--, case exciResponse when 'C' then 1 end c
--, case exciResponse when 'D' then 1 end D
--, exciResult 
--from ExamCandidateItems I
--  INNER JOIN ExamCandidates C
--      ON I.excID = C.excID
--WHERE exID = 32
--ANd exItemCode = 'ITEM_001_MS0401010101M_AAA'


DECLARE @F TABLE
(
    excID uniqueidentifier
)

Declare @t TABLE
(
exID int
, item nvarchar(100)
, A int NULL
, B int NULL
, C int NULL
, D int NULL
, E int NULL
, Answer nvarchar(1)
, Responses int
, Correct int
, CorrectPerc decimal(6,3)
, Variance float
)

Declare @B TABLE
(
exID int
, B nvarchar(100)
, Responses int
, Correct int
, Items int
, CorrectPerc decimal(6,3)
, GroupVariance float
, SumItemVariance float
, CronbachAlpha float
)

INSERT INTO @F Select top 10000 excID from ExamCandidates WHERE exID = @ID

INSERT INTO @t
select exID
, substring(exItemCode,10,8)
, sum(case exciResponse when 'A' then 1 end) A
, sum(case exciResponse when 'B' then 1 end) B
, sum(case exciResponse when 'C' then 1 end) C
, sum(case exciResponse when 'D' then 1 end) D
, sum(case exciResponse when 'E' then 1 end) E
, min(case when exciResult = 1 then exciResponse end) Answer
, count(*) Responses
, sum(exciResult) Correct
, convert(float,sum(exciResult) * 100)/count(*) CorrectPerc
, var(exciResult) Variance
from ExamCandidateItems I
    INNER JOIN ExamCandidates C
        ON I.excID = C.excID
WHERE exID = @ID
AND I.excID in (Select excID from @F)
GROUP BY exID, exItemCode
ORDER BY exID, exItemCode

Select * from @t
ORDER BY item



--Select excID
--, Item_001, Item_002, Item_003, Item_004, Item_005, Item_006, Item_007, Item_008, Item_009, Item_010
--from
--(
--select I.excID
--, left(exItemCode,8) Item
----, sum(case exciResponse when 'A' then 1 end) A
----, sum(case exciResponse when 'B' then 1 end) B
----, sum(case exciResponse when 'C' then 1 end) C
----, sum(case exciResponse when 'D' then 1 end) D
----, sum(case exciResponse when 'E' then 1 end) E
----, min(case when exciResult = 1 then exciResponse end) Answer
--, sum(exciResult) Correct
----, convert(float,sum(exciResult) * 100)/count(*) CorrectPerc
----, var(exciResult) Variance
--from ExamCandidateItems I
--  INNER JOIN ExamCandidates C
--      ON I.excID = C.excID
--WHERE exID = 32
----ANd exItemCode = 'ITEM_001_MS0401010101M_AAA'

--GROUP BY I.excID, exItemCode

--) Src
--PIVOT  
--(  
--  sum(Correct)  
--  FOR Item in (Item_001, Item_002, Item_003, Item_004, Item_005, Item_006, Item_007, Item_008, Item_009, Item_010)
--) AS PivotTable;  

select I.excID
, substring(exItemCode,10,8) B
--, sum(case exciResponse when 'A' then 1 end) A
--, sum(case exciResponse when 'B' then 1 end) B
--, sum(case exciResponse when 'C' then 1 end) C
--, sum(case exciResponse when 'D' then 1 end) D
--, sum(case exciResponse when 'E' then 1 end) E
--, min(case when exciResult = 1 then exciResponse end) Answer
, sum(exciResult) Correct
, count(*) Responses
, count(Distinct exItemCode) Items
, convert(float,sum(exciResult) * 100)/count(*) CorrectPerc

from ExamCandidateItems I
    INNER JOIN ExamCandidates C
        ON I.excID = C.excID
WHERE exID = @ID
AND I.excID in (Select excID from @F)

GROUP BY I.excID,  substring(exItemCode,10,8) 

INSERT INTO @B
(
exID
, B
, Responses
, Correct 
, Items
, CorrectPerc 
, GroupVariance 
)
Select 
exID
, B
, sum(Responses) Responses
, sum(Correct) Correct
, min(Items)Items
, convert(float,sum(Correct) * 100)/sum(Responses) CorrectPerc
, var(Correct) GroupVariance
FROM
(
select exID
, I.excID
, substring(exItemCode,10,8) B
--, sum(case exciResponse when 'A' then 1 end) A
--, sum(case exciResponse when 'B' then 1 end) B
--, sum(case exciResponse when 'C' then 1 end) C
--, sum(case exciResponse when 'D' then 1 end) D
--, sum(case exciResponse when 'E' then 1 end) E
--, min(case when exciResult = 1 then exciResponse end) Answer
, sum(exciResult) Correct
, count(*) Responses
, count(Distinct exItemCode) Items
, convert(float,sum(exciResult) * 100)/count(*) CorrectPerc

from ExamCandidateItems I
    INNER JOIN ExamCandidates C
        ON I.excID = C.excID
WHERE exID = 32
AND I.excID in (Select excID from @F)

GROUP BY exID, I.excID,  substring(exItemCode,10,8) 
) SUB
GROUP BY exID, B
ORDER BY exID, B


UPDATE @B
set sumItemVariance = T.SumItemVariance
, CronbachAlpha = convert(float,Items) / (ITems- 1) * (1 - (T.sumItemVariance / GRoupVAriance))
FROM @B
INNER JOIN
(
Select Item, sum(Variance) SumItemVariance
FROM @T
GROUP BY ITem
) T
ON [@B].B = T.Item



Select * from @B

‌

Obviously this forms the basis of a permanent addition to warehouse upload, possibly producing these new analysis tables (@T, @b above)

[Ghislain Hachey]

@Brian Lewis i sent the link to a private share where you should be able to get plenty of workbooks that include the Test Analysis sheet.

[Brian Lewis]

This display is repeated for each question on the exam on new tab ‘Test Analysis’

This provides all the item analysis off the Soe workbook Test Analysis :

• Number of candidates for each response;
• percent of candidates for each response,
• assessment of Hard/Moderate/Easy,
• identification of ‘strong distractors’

The ‘correct answer is highlighted in the table, and same color in chart. Otherwise colors are consistent for each response A B C D E F - ie the default color is overriden by the 'Correct color’ in the chart

The ‘p-value’ ; ie the percentage correct, is clearly shown in the table.

Strong distractors are highlighted in the table (bold red). These are items where the number of responses for that distractor > the number of correct responses.

The Easy Moderate Hard classification is built into the Item Code. This is displayed.

In Soe workbook, the EMH is assessed as :

< 33.3 => Hard

< 66.6 => Moderate

<-100 => Easy

If the assessed EMH matches the expected a tick is shown. Otherwise the assessed EMH is shown

‌

[Brian Lewis]

Calculation of Cronbach’s Alpha and its related Standard Error of Measurement:

Note that Cronbacah’s Alpha gives the same result as Kudur Richardson KR-20 when the items only take binary values 1 or 0.. So there is no point presenting both!

[Brian Lewis]

Calculation of Discrimination Index:

This is calculated for each item.

This is NEW - not currently calculated in Soe workbook.

[Brian Lewis]

feat(exams):Test Analysis, including Cronbach Alpha, Discrimination Index, distractor analysis, difficulty assessment of items

Resolves #1163

→ <<cset daa4c66d892c>>

[Brian Lewis]

sql: Exam item analysis

Resolves #1163

→ <<cset c725c9be9dd0>>