DISTINCT application_no

[#1]

count(DISTINCT application_no If(new_dates=settled_dates,'Y'))

what is the best way to do this calculation in an expression

[peter_turner]

Hello,

As this is an expression in your UI you could use Set Analysis to perform this, such as

count(DISTINCT {$<new_dates=settled_dates>} application_no)

This will work when you have values selected in your new_dates field, but wont work if your new_dates field doesn't have spaces.

(also the above expression might show with an error by the syntax checker in the expression editor, best to confirm the number in a simple text box whilst developing it)

[sunny_talwar · Accepted solution]

Try this:

=Count(DISTINCT If(new_dates = settled_dates, application_no))

HTH

Best,

S

[#4]

you guys rock, the code work for what i want to do

[sunny_talwar]

Awesome, I am glad we were able to help.

I would suggest closing the thread by marking it correct for whichever solution worked for you the best.

Best,

S

[#6]

Hi

Check With the expression

count(DISTINCT {$<new_dates=settled_dates>} application_no)----Wrong Expression

=Count(DISTINCT If(new_dates = settled_dates, application_no)) --- Correct Expression

It Will work

[peter_turner]

Both expressions actually work, but it depends where you're using them, such as text boxes or a chart.

For example the expressions without SetAnalysis will give a result in a text box where no selections are made (like a total version), but the SetAnalysis expression will only produce a value when you make a selection on the first field new_dates.

Both good examples