IF Count function - counting one criteria and counting rest of...

[infock12]

Dear community,

Good morning and hope you had a good weekend!

I have a list of questions and the scores scored by different teams. The criteria is below.

IF Team A has scored 10 marks for at least 3 questions. AND scored at least 7 to 9 marks for the REST OF QUESTIONS - Say Excellent, otherwise Good.

The query I tried is - if(count({<Marks = {'10'}>}Questions)>=3 and count({<Marks = {'>=7 <=9'}>}Questions), 'Excellent, 'Good')

This didn't work, not sure how to count 'rest of the questions'. I have attached the data and a sample pivot table I am trying to create.

Please help.

[infock12]

Hi Sasi,

I tried this and all category is showing as 'Good.' I am trying to make sense of the second part in this section of your answer. I would appreciate if you could explain it in lay man's terms.

((count({<Marks = {">=7<=9"}>} Questions)- (count(TOTAL<Department,Category> Questions)-count({<Marks = {"10"}>} Questions)))=0)

The first count is counting questions with marks between 7 and 9. (SUBTRACT)

The second part is counting ALL the questions irrespective of the score? SUBTRACT

The third part counts the questions that have a score of 10?

Is my understanding right? What if there are no questions with a score of 10, and 80% of questions with 7 to 9 scores? count(TOTAL<Department,Category> Questions) will give 100%, so won't it become 80%-100%?

Sorry if I am being naïve but just trying to be clear. Thanks!

Karthik

[infock12]

Hi Qlikviewwizard,

Team here refers to 'Department' in the data. The pivot gives 'Department' on rows and 'Category' on columns.

Hope this helps!

[sasiparupudi1]

Hi Karthik,

Yes, Thats what it will do.

As per my understanding, for example,

out of 50 questions

if 3 are 10 marks

47 must have marks between 7 and 9 to be marked as excellent

so count questions with marks 10 will give 3

total count will give you 50

total marks between 7 and 9 if say are 40

then the 50-40-3=7 which will put that in the good category

on the other hand ,if total marks between 7 and 9 returns 47,

50-47-3=0 which will put that in the  excellent category.

I hope my understanding is correct

Sasi

[infock12]

Thanks Sasi, that works!

The below works as well. Looks a little straightforward to me, but there may be something additional in your query that may help. I will let you know if any issues arise.

if(count({<Marks = {'10'}>}Questions)>=3 and count({<Marks = {'<=6'}>}Questions)=0, 'Excellent', 'Good')