8 Replies Latest reply: Aug 2, 2011 3:59 PM by Stefan Wühl RSS

    counting distinct based on 2 fields?

      Hi everyone,

       

      I am facing a problem in counting distinct numbers as I don't know the exact syntax. I have data that looks like:

       

      CandID  Date

      1.          2/6/10

      1.          2/6/10

      1.          2/6/10

      2           2/6/10

      3           3/6/10

       

      So as you can see candid is repeating for the same date. I want to use an expression, that could count distinct Candid , but if a candid is repeated 3 time (as in the above case) on a single date, it should count it as one.

       

      So, it should count 2 candid for the date 2/6/10 in the above example.

       

      so something like count (distinct CandID further based on distinct date)

       

      thanks. any repleis would be appreciated.