Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2005
    Posts
    1

    Exclamation 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?

    Thanks,

    Robert
    Last edited by rkuzel; 01-05-05 at 10:04.

  2. #2
    Join Date
    Jan 2005
    Location
    Avon Lake, OH
    Posts
    112
    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?

    Global NumberVar Count1;

    If {@M_Startdate} <= {@ClientRefDate} And {@ClientRefDate} <= {@M_EndDate} Then
    Count1 := Count1 + 1;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •