counting distinct based on 2 fields?

[#1]

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.

[swuehl]

Is it possible that in your example CandID 1 appears also on a date different than 2/6/10?

If not, a

count (distinct CandID)

should be enough, shouldn't it?

If there are multiple dates to distinguish, you could use a concatenation between CandID & Date and count distinct that one.

[#3]

yes there are multiple dates on which same CandID is present, thats what the problem was. Can you write the syntax for how to concatenate CandId and date and count distinct that??

thanks so much for your reply.

[swuehl]

Nothing special,

try

=count(distinct CandID&'-'&Date)

[rahulgupta]

Hey Hi,

Use this:

=Aggr(Count(Distinct CandID),Date)

Hope this helps....

Regards

[#6]

Small changes to the Rahul`s code. Rahul`s code will give you an correct value when you use the expression in pivot / Straight table. but it will not give you the value when the same expression is used in text box, gauge chart and certain scenarios. Covering sum with Rahul`s expression will do the job perfectly for you.

             Sum(Aggr(Count(Distinct CandID),Date))

- Sridhar

[#7]

Hello All,

I used all the above expressions you suggested and I am getting different values for each expression. I am creating a simple chart(car graph), which expression among all will give me the right result in this situation??

thanks all for the help.

-Yaman

[swuehl]

So you looking for a expression within a chart (by the way, what is a car graph?).

What dimension(s) do you use?

Our Expressions might give different results depending on your dimensions. What is correct is depending what you want to achieve.

Could you explain a bit closer, what your are trying to achieve with that expression (e.g. count all IDs disregarding the charts dimension, if necessary)?

Would be good if you could post a small sample app here.

Cheers,

Stefan

[swuehl]

Ok, forget my question concerning the bar graph, I was really thinking that there might be a new extension

I think my expression should be correct if you want a distinct count, taken the dimensions correctly into account.

The expression of Sridhar should actually be changed to

=Sum(Aggr( Count(Distinct CandID),Date,CandID))

to be equivalent. Otherwise you would get different (in normal use IMHO "wrong" results) if you use a CandID dimension in your graph.

In this case, I think my expression is shorter

Regards,

Stefan