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.

[jonathandienst]

How about:

If(Count(If(Marks = 10, Questions)) > 3 And Count(If(Marks <= 6, Questions)) = 0, 'Excellent', 'Good')

OR

If(Count({<Marks = {10}>} Questions) > 3 And Count({<Marks = {"<= 6"}>} Questions) = 0, Excellent', 'Good')

[infock12]

Hi Jonathan,

Thanks for the quick response. I will try it and let you know.

[jagan]

Hi,

Try this expression

If(count({<Marks = {'10'}>} Questions) >= 3 AND count({<Marks={'>6<10'}>}Questions), 'Excellent', 'Good')

Hope this helps you.

Regards,

Jagan.

[infock12]

Hi Jagan,

Thanks for that. Your expression is something similar to what was in my question, but unfortunately it is not working. I have one team which have 4 questions with a score of 10, 50 questions with a score of 8 and 3 questions with a score of 2. Your (and mine in my question) expression is giving an output of 'Excellent.' Out of 57 total questions for this team, 3 questions have a score of less than 3 so the output should not be excellent.

However, Jonathan's expression works fine. I am yet to write the complete expressions, but so far it works well.

Thanks for your help again.

[infock12]

Hi Jonathan,

It works well so far, and once I wrote the complete expression, which is quite complicated, I will let you know if any complications. Thanks for your help again!

Karthik

[sasiparupudi1]

if (count({<Marks = {"10"}>} Questions) >= 3 and (count({<Marks = {">=7<=9"}>} Questions)- (count(TOTAL<Department,Category> Questions)-count({<Marks = {"10"}>} Questions)))=0 ,'Excellent','Good')

[infock12]

That's great, thanks Sasidhar. I will give it a try.

[sasiparupudi1]

hi

Did you manage to solve your problem?

Sasi

[qlikviewwizard]

Hi jagan,SasiParupudi1

I did not understand the Team A in the question and also solution.

Could any one help me how to use the expression in report(pivot/straight table).

Thanks in advance.