topic Re: IF Count function - counting one criteria and counting rest of... in QlikView

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

infock12 — Mon, 03 Aug 2015 08:59:42 GMT

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.

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

jonathandienst — Mon, 03 Aug 2015 09:06:45 GMT

How about:

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

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

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

infock12 — Mon, 03 Aug 2015 09:12:27 GMT

Hi Jonathan,

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

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

jagan — Mon, 03 Aug 2015 09:26:45 GMT

Hi,

Try this expression

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

Hope this helps you.

Regards,

Jagan.

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

infock12 — Mon, 03 Aug 2015 10:44:11 GMT

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.

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

infock12 — Mon, 03 Aug 2015 10:45:54 GMT

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

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

sasiparupudi1 — Mon, 03 Aug 2015 13:40:40 GMT

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

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

infock12 — Mon, 03 Aug 2015 13:57:26 GMT

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

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

sasiparupudi1 — Tue, 04 Aug 2015 19:54:19 GMT

Did you manage to solve your problem?

Sasi

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

qlikviewwizard — Wed, 05 Aug 2015 06:10:02 GMT

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.

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

infock12 — Wed, 05 Aug 2015 08:39:20 GMT

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

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

infock12 — Wed, 05 Aug 2015 08:47:30 GMT

Hi Qlikviewwizard,

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

Hope this helps!

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

sasiparupudi1 — Wed, 05 Aug 2015 08:55:40 GMT

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

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

infock12 — Wed, 05 Aug 2015 09:22:12 GMT

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