If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: DistinctCount issue

Hi there,

I'm having a problem with a pretty easy formula which is suppose to just Distinct Count some ID's for a specific period of time:
Each parameter in the formula represents a field in the database, formula bellow should return 3 records... but it returns 7. If I take out the date conditions (so only DistinctCount remains) (7) records will be returned.

If {@M_Startdate} <= {@ClientRefDate} and {@ClientRefDate} <= {@M_EndDate} Then DistinctCount({@UniqueRefID})

I also tried to put is in a different way (which express the same thing);If {@ClientRefDate} in [{@M_Startdate} to {@M_EndDate}] Then DistinctCount({@UniqueRefID})

It looks like a date validation is performed when I reverse the <= with >=; Formula bellow returns (0) records, if I remove any date condition (7) records are returned.If {@M_Startdate} >= {@ClientRefDate} and {@ClientRefDate} <= {@M_EndDate} Then DistinctCount({@UniqueRefID})

Using WhilePrintingRecords; right at the top does not help I tried already. Also adding a date validation on the main query of the report does not help because whole data will be affected.
Any suggestions?

I think DistinctCount will only work on all data, all data in a group, etc., so your condition is being evaluated correctly to decide whether to perform DistinctCount, but the condition does not filter down to the count itself.

So you will either get all of them counted (7) or none!

How about using one or more global variables, and perform the count 'manually', maybe in the details section?