Exceptions in a set expression

[malradi88]

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

[tomasz_tru]

Is it possible to create flag for correct answers in the data model?

[malradi88]

Hi Tomasz,

Thank you for your reply. I have never created a flag before for correct answers. What function would that entail?

Best,

MOhammed

[sonkumamon]

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])

[malradi88]

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];

[antoniotiman]

Hi,

what about

(Count({<Question={1},answer={'false'}>} answer)+Count({<Question={2},answer={'true'}>} answer)+.......)

/Count(ansver)

Regards,

Antonio

[malradi88]

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.

[antoniotiman · Accepted solution]

Do you need brackets

(Count+Count+.......)/Count(Answer) ?

[malradi88]

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)

[antoniotiman]

I'm glad to be able to help You.

Regards,

Antonio