topic Re: Using COUNT and BETWEEN function together in QlikView

Using COUNT and BETWEEN function together

infock12 — Wed, 15 Jul 2015 09:51:43 GMT

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.

Re: Using COUNT and BETWEEN function together

Gysbert_Wassenaar — Wed, 15 Jul 2015 09:56:19 GMT

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)

Re: Using COUNT and BETWEEN function together

sunny_talwar — Wed, 15 Jul 2015 10:04:44 GMT

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??

Re: Using COUNT and BETWEEN function together

infock12 — Wed, 15 Jul 2015 10:19:43 GMT

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!

Re: Using COUNT and BETWEEN function together

infock12 — Wed, 15 Jul 2015 11:31:35 GMT

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.

Re: Using COUNT and BETWEEN function together

sunny_talwar — Wed, 15 Jul 2015 11:46:18 GMT

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

Re: Using COUNT and BETWEEN function together

qlikmsg4u — Wed, 15 Jul 2015 11:56:50 GMT

Find the attached App

Re: Using COUNT and BETWEEN function together

infock12 — Wed, 15 Jul 2015 12:12:17 GMT

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

Re: Using COUNT and BETWEEN function together

infock12 — Wed, 15 Jul 2015 12:13:32 GMT

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!

Re: Using COUNT and BETWEEN function together

infock12 — Wed, 15 Jul 2015 13:03:50 GMT

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.

Re: Using COUNT and BETWEEN function together

sunny_talwar — Wed, 15 Jul 2015 13:14:24 GMT

I am not sure I completely understand your conditions. Would you be able to list out all possible conditions with there expected results if there are not too many of them. For instance with this if statement

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

The first statement means that

If Count of questions is between 10 and 19 and if the Mark 10 Scores are more than 2 then its excellent.

Likewise can you list all your conditions....

Re: Using COUNT and BETWEEN function together

infock12 — Wed, 15 Jul 2015 13:26:59 GMT

Sorry for not being so clear.

You have got the first statement right, which is

If Count of questions is between 10 and 19 and if the Mark 10 Scores are more than 2 (AND IF ALL THE REST OF THE QUESTIONS HAVE MARKS BETWEEN 7 AND 9) then it is excellent, otherwise 'Good' - I have added the condition I wanted to add in brackets and capitalised.

I tried the below and it works but wanted to confirm if it is the right way to go? Or if there is an alternative way?

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

The whole exercise is a bit complicated and it keeps evolving as I go along!

Re: Using COUNT and BETWEEN function together

sunny_talwar — Wed, 15 Jul 2015 13:31:05 GMT

Try if this works:

If((Count(Questions)>10 and Count(Questions)<20 and Count({<Rating = {'10'}>} Questions)>=2) or

(Count({<Rating = {'>=7<=9'}>} Questions) >=3 and Count({<Rating = {'>=7<=9'}>} Questions) <20), 'Excellent', 'Good'))))

Re: Using COUNT and BETWEEN function together

infock12 — Wed, 15 Jul 2015 13:59:28 GMT

Hi,

Sorry, connecting with OR statement didn't work, but the one I sent in the earlier post works fine.

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

Typed in as Rating instead of Marks in my previous post.