Count expression with multiple nested if statements

[jessica_webb]

I have a very complex expression (for me anyway) that I am really struggling with and I'm hopeful that someone might have a solution. I would be very happy with either a working expression, or a suggestion as to how to create a new field in the script to give the same result.

Here is the problem:

I have 4 fields, all with many duplicates on rows:

Candidate

Code

Flag

Points

E.g.

Row	Candidate	Code	Flag	Points
1	AB1	kdjf87	Yes	250
2	AB1	djf9083u	No	220
3	AB1	sdj2983	Yes	280
4	AB1	klhg987	Yes	290
5	CD2	sdfa45	Yes	250
6	CD2	sdfa235	Yes	260
7	CD2	fgah235	Yes	255
8	CD2	tjsjs235	Yes	280

I want a bar chart that shows the number of candidates who meet the following criteria:

If a candidate has 3 distinct 'Code' fields, where 'Flag = 'Yes'' and 'Points >=240

and

has 2 distinct  'Code' fields, where 'Flag = 'Yes'' and 'Points >=270

With my table above, Candidate AB1 would count as meeting criteria, because of rows 1, 3 and 4.

However Candidate CD2 would not because although they meet the first criteria on rows 5, 6, 7 and 8, they do not meet the second criteria.

So far I've come up with:

Count(Distinct(If(

Sum(If(Flag = 'Yes' and Points >=240)) >=3

and

Sum(If(Flag = 'Yes' and Points >=270)) >=3)) Candidate)

But no luck so far.

I won't be able to respond to any suggestions until after the new year, but if anyone is able to think this over for me, I would be very grateful!

[Gysbert_Wassenaar · Accepted solution]

Try:

count({<Candidate={"=count({<Flag={'Yes'},Points={'>=240'}>}distinct Code)=3"}>*<Candidate={"=count({<Flag={'Yes'},Points={'>=270'}>}distinct Code)=2"}>}distinct Candidate)

[sunny_talwar]

Try this expression:

=Count(DISTINCT {<Candidate = {"=Count({<Flag = {'Yes'}, Points = {'>=240'}>}Candidate) >= 3"}*{"=Count({<Flag = {'Yes'}, Points = {'>=270'}>}Candidate) >= 2"}>} Candidate)

Taking a leaf out of Gysbert's book here:

=Count(DISTINCT {<Candidate = {"=Count(DISTINCT {<Flag = {'Yes'}, Points = {'>=240'}>}Code) >= 3"}*{"=Count(DISTINCT {<Flag = {'Yes'}, Points = {'>=270'}>}Code) >= 2"}>} Candidate)

[#4]

Hi Jessica, I think that if you want to show the number of candidates that satisfy this conditions you can do the following in a text box:

= if (  (count(distinct CODE)=3 and Flag="Yes" and Points>240 , sum (distinct candidate ) )

+

if (  (count(distinct CODE)=2 and Flag="Yes" and Points>270 , sum (distinct candidate ) )

I hope that it help you!

Regards,

Agustin

[jessica_webb]

Thanks so much Gysbert - worked almost perfectly (just had to change 'distinct Code)=3' to 'distinct Code)>=3'

Fantastic solution - greatly appreciated.

[jessica_webb]

Hi Sunny,

Thanks for posting the edit there. Took me a while to work out why it wasn't working as I wanted it to before I realised... Should have read your message first

Thanks again for your help!

[jessica_webb]

Thanks for your response Agustin.

I did give it a go, but something wasn't working quite right (it failed at 'Points>240'), but haven't investigated as the first answer I was given has worked great.

Thanks again!

[jessica_webb]

Hi Gysbert,

I was hoping you might be able to answer a question I had about the expression you suggested. As I said, it seems to working perfectly. I have now tried to adapt it for another graph I need, where the criteria are similar (but not the same) and there is only one main condition (i.e the second part of the expression is not needed).

The expression is:

count({<Location={'London'},Candidate={"=count({<Type={'Old'},Points={'>=150'}>}distinct Code)>=2"}>}distinct Candidate)

Another chart shows:

count({<Location={'London'},Candidate={"=count({<Type={'New'},Points={'>=150'}>}distinct Code)>=2"}>}distinct Candidate)

I thought this was also working ok, however I have a list box for 'Type' field, and when I select 'New', the graph with the first expression still shows a value, which I wouldn't expect as it should only be for Type 'Old'.

Looking at the expression, the values in single quotation marks (e.g. 'Old') are in the reddish text, rather than black as they normally are. Could this be making a difference? However, 'London' is in black text as expected.

Are you able to see where I'm going wrong? And are you able to explain what the blue, red and black font means within a QV expression? I've googled this, but can't find an explanation.

Many thanks,

Jess

[Gysbert_Wassenaar]

Try adding that set modifier for Type to outer expression too:

count({<Type={'Old'},Location={'London'},Candidate={"=count({<Type={'Old'},Points={'>=150'}>}distinct Code)>=2"}>}distinct Candidate)

That way it's not only used in the search expression for the Candidate field, but also applied to the outer count expression.

Keywords are blue and so are reference to expression labels in chart expression, field names are in red as are text strings in double quotes. That's because double quotes can also be use to mark field and table names, just as [ ] and ` can be used to quote field and table names. It's necessary to use quotes if a field name contains one or more space characters. The rest of an expression like values in single quotes are shown in black.

[jessica_webb]

Perfect solution - thank you so much!

Also a really helpful explanation. Will keep a note of that somewhere and hopefully it'll help me when writing my own expressions.

Thanks again,

Jess