Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Qlik Community,
I am working on a dataset that consists of three columns:
1)name
2) question
3) answer
The answers are all either true and false. I am trying to create a master measure that measures % correct answers. The difficulty in this is that the correct answer differs for each question...some are 'false and others 'true', so I cannot simply instruct Qlik to do for example:
Count ({$<answer = {'true'})>}[answer])/ Count ([answer]) and treat the percentage produced as 'correct' because not all correct answers should be answered 'true'.
I would need to instruct Qlik to count true as correct for some questions and false as correct for other questions. There are 5 questions under the question field. Based on the three fields provided above (name, question, answer) how could I make an expression that takes this into account.
For example, lets say I wanted to amend the above expression and within the first segment (the set expression) instruct qlik to count :
answers for question 1 that are false
answers for Question 2 that are true
answers for Question 3 that are false
Answers for Question 4 that are true
Answers for Question 5 that ae true
because those aforementioned answers are correct. Would it be some really long if statement within the set expression? If so would you be able to provide a rough example? Are there other ways of doing this? Perhaps through the dataload editor? Some sort of inline table?
Apologies I am new to this! Thank you for your support.
Best,
Mohammed
Is it possible to create flag for correct answers in the data model?
Hi Tomasz,
Thank you for your reply. I have never created a flag before for correct answers. What function would that entail?
Best,
MOhammed
Hi, to create a flag which flags the right answer to each question, add to the LOAD script:
Table:
LOAD
If((Question = 1 AND Answer = 'false')
OR (Question = 2 AND Answer = 'true')
OR (Question = 3 AND Answer = 'false')
OR (Question = 4 AND Answer = 'true')
OR (Question = 5 AND Answer = 'true'), 1, 0) AS CurrectAnswerFlag
FROM [*.qvd]
Now you can write the following expression which divides the right answers (Flag = 1) by the total aswers:
Count ({$< CurrectAnswerFlag = {'1'})>}[answer])/ Count ([answer])
Hi Uri,
Thank you for that.
I have tried to recreate the above in my load script (within the existing table and then as a new table) but on luck with both. I believe I am getting the syntax wrong. Is there anyway other way you would write it? Thank you.
Within Existing Table:
[Original Table]:
LOAD
[Name],
[Question],
if (([Question] = 'Question1' AND [Answer]= '1')
OR([Question] = 'Question2' AND [Answer] = '2')
OR ([Question] = 'Question3' AND [Answer] = '1') as correctanswer
[Answer]
FROM [lib://Desktop/testcurrentflag.xlsx]
(ooxml, embedded labels, table is Sheet1);
As a new Table:
[Original Table]:
LOAD
[Name],
[Question],
[Answer]
FROM [lib://Desktop/testcurrentflag.xlsx]
(ooxml, embedded labels, table is Sheet1);
[AnswerFlagtable]:
Load *,
if (([Question] = 'Question1' AND [Answer]= '1')
OR([Question] = 'Question2' AND [Answer] = '2')
OR ([Question] = 'Question3' AND [Answer] = '1') as correctanswer
Resident [Original Table];
Drop table [Original Table];
Hi,
what about
(Count({<Question={1},answer={'false'}>} answer)+Count({<Question={2},answer={'true'}>} answer)+.......)
/Count(ansver)
Regards,
Antonio
Hi Antonio,
Thanks for that. I tried your expression in a master measure:
Count({<Question= {'Question 1'}, Answer={'1'}>}Answer) +
Count({<Question= {'Question 2'}, Answer={'2'}>}Answer) +
Count({<Question= {'Question 3'}, Answer={'1'}>}Answer)
/ Count (Answer)
but got a wrong answer. It is strange because separately (without the division) the respective expressions work i.e. all the additions prior to the division sum to 5 which is correct and the 'Count(Answer)' alone sums to 9 which is correct. However when I divide them I get the answer 3.22 when 5/9 should equal .55
Anything that could be changed to make the result correct? Thank you for your support.
Do you need brackets
(Count+Count+.......)/Count(Answer) ?
Thank you Antonio! it worked when I added brackets around the entire + part:
(Count({<Question= {'Question 1'}, Answer={'1'}>}Answer) +
Count({<Question= {'Question 2'}, Answer={'2'}>}Answer) +
Count({<Question= {'Question 3'}, Answer={'1'}>}Answer))
/ Count (Answer)
I'm glad to be able to help You.
Regards,
Antonio