Using COUNT and BETWEEN function together

[infock12]

Hello all,

Good morning! I would like to use COUNT and BETWEEN function together. I am trying to count the total number of questions and apply another condition. For example,

if(count(Questions)>"=4 <=7", if(Count({<Marks = {'10'}>}Questions)>=3, if(Count({<Marks = {'7', '8', '9', '10'}>}Questions)>3, 'Excellent', 'Good.'

I wanted to say if the total number of questions are between 4 and 7, and

if >3 questions have more than 10 marks,

and if the rest all the questions have more than 7 marks, say 'Excellent', otherwise 'Good'.

Two questions:

1. The above function does not work, and it seems that the problem lies in the first IF and between,  i.e., [if(count(Questions)>"=4 <=7"]. If I apply if(count(Questions)>=3, it works.

2. The third IF statement, i.e., if(Count({<Marks = {'7', '8', '9', '10'}>}Questions) works fine, but how can I count the marks that are more than 7? Rather than saying Marks={'7', '8', '9', '10'}. When I gave {>7}, it does not work.

t doesn't stop there, I will have to write this for questions less than 4, and questions more than 7 but if I can get the first part right, I can build the rest.

Thanks for your help in advance.

[Gysbert_Wassenaar]

You'll have to split that into two conditions with an AND:

if(count(Questions)>4 AND  count(Questions)<=7, ....

To count the mark larger  than or equal to 7 use:
if(Count({<Marks = {'>=7'}>}Questions)

[sunny_talwar]

If what Gysbert‌ has shared with you doesn't solve your problem, would you be able to share a sample data with your expected output??

[infock12]

Thanks Gysbert for the quick reply!

Hi Sun, I will try and work on it and put the sample if it doesn't work. Thanks again!

[infock12]

Hi Gysbert and Sunindia,

The syntax seems to be working but not getting the desired output. I am using the following expression and I am sure by looking at the script, you will be able to ascertain what I am trying to get. I have attached the files too.

=if(count(Questions)>=10 AND  count(Questions)<20, if(Count({<Marks = {'10'}>}Questions)>=2, 'Excellent',
if(count(Questions)>=20 AND  count(Questions)<40, if(Count({<Marks = {'10'}>}Questions)>=4, 'Excellent',
if(count(Questions)>=40 AND  count(Questions)<60, if(Count({<Marks = {'10'}>}Questions)>=6, 'Excellent',
if(count(Questions)>=10 AND  count(Questions)<20, if(Count({<Marks = {'>=7 <=9'}>}Questions)>=3, 'Good',
'Insufficient'))))))))

The script will be much longer than this and I will have to include multiple combinations, but I thought I would start with this. Not sure what I am doing wrong. Any help is much appreciated. I want the result to look something like the below.

[sunny_talwar]

You need to change your if statement to something like this:

=if(count(Questions)>=10 AND  count(Questions)<20 and Count({<Marks = {'10'}>}Questions)>=2, 'Excellent',

if(count(Questions)>=20 AND  count(Questions)<40 and Count({<Marks = {'10'}>}Questions)>=4, 'Excellent',

if(count(Questions)>=40 AND  count(Questions)<63 and Count({<Marks = {'10'}>}Questions)>=4, 'Excellent',

if(count(Questions)>=10 AND  count(Questions)<20 and Count({<Marks = {'>=7 <=9'}>}Questions)>=3, 'Good',

'Insufficient'))))

[qlikmsg4u]

Find the attached App

[infock12]

Thanks Sunindia, I will try that and let you know.

[infock12]

Hi,

I think you missed the attachment. Besides, I am using the developer edition, so I would appreciate if you could post the expression.

Thanks!

[infock12]

Hi all,

It works, which is great! However, I am confronted with another issue. The rule says:

If I have 4 to 8 questions, and if 2 or more has 10 marks and ALL THE REST of the questions has marks between 7 and 9, say 'Excellent', otherwise 'Good'.

The first and second part is fine which is:

If(count(Questions)<=3 AND count(Questions)<=8 AND count({<Marks = {'10'}>}Questions)>=2 AND ...

not sure how to write the last part to say...if the REST OF THE QUESTIONS have marks between 7 and 9, say 'excellent', or 'good'.

I thought of adding, count({<Marks = {'>7 <9'}>}Questions)">=5 <=8", 'Excellent', 'Good') but not sure if I am doing the right thing.