Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
count(DISTINCT application_no If(new_dates=settled_dates,'Y'))
what is the best way to do this calculation in an expression
Try this:
=Count(DISTINCT If(new_dates = settled_dates, application_no))
HTH
Best,
S
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)
Try this:
=Count(DISTINCT If(new_dates = settled_dates, application_no))
HTH
Best,
S
you guys rock, the code work for what i want to do
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
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
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