Match

[ramprakashs]

Hi When applying the following query i should get the count of projects but I am getting '-'

IF(MATCH(CONTRACTSTAGE, '30 / Pre-Tender'), Count(distinct PROJECTID))

Please correct me my mistake.

Thanks in advance.

[sunny_talwar · Accepted solution]

Try this

Count(DISTINCT {<CONTRACTSTAGE = {'30 / Pre-Tender'}>} PROJECTID))

[ramprakashs]

Thanks it worked

[ramprakashs]

Also how to add additional condition along with it?

[sunny_talwar · Accepted solution]

Like this

Count(DISTINCT {<CONTRACTSTAGE = {'30 / Pre-Tender'}, Condition2Field = {'abc'}, Condition3Field = {'xyz'}>} PROJECTID))

[ramprakashs]

Value	New Bidder Total	Total Projects with Bidders	Pre-Tender	Applications to Tender
Small	4	4	0	0
<£1m	10	9	2	0
£1m-£10m	28	23	0	0
£10m-£50m	5	4	1	0
£50m-£200m	0	0	0	0
Mega	2	1	0	0

I need my output like this, while I tried

IF(MATCH(CONTRACTSTAGE,'32 / Tender Currently Invited', sub_val, 'A', Value, 'Small'),COUNT(Distinct PROJECTID)
,(IF(MATCH(CONTRACTSTAGE,'32 / Tender Currently Invited', sub_val, 'B', Value, '<£1m'),COUNT(Distinct PROJECTID)
,(IF(MATCH(CONTRACTSTAGE,'32 / Tender Currently Invited', sub_val, 'C', Value, '£1m-£10m'),COUNT(Distinct PROJECTID)
,(IF(MATCH(CONTRACTSTAGE,'32 / Tender Currently Invited', sub_val, 'D', Value, '£10m-£50m'),COUNT(Distinct PROJECTID)
,(IF(MATCH(CONTRACTSTAGE,'32 / Tender Currently Invited', sub_val, 'E', Value, '£50m-£200m'),COUNT(Distinct PROJECTID)
,(IF(MATCH(CONTRACTSTAGE,'32 / Tender Currently Invited', sub_val, 'F', Value, 'Mega'),COUNT(Distinct PROJECTID))))))))))))

I am getting '-' in the output, What did I missed?

[sunny_talwar]

I am not sure, would you be able to share a qvw or qvf file to show what you have to help you better?

[sunny_talwar]

Which chart in the sample is having issues and what is that you are looking to get in that chart based on the data shared?

[ramprakashs]

The second one i need it based on contractstage, sub_val and value.

i.e: for Contract Stage = '30 / Pre-Tender' sub_val = 'A' and value='Small'

This should be done for 6 Values and 12 Contract Stages.