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

# 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.

• ###### counting distinct based on 2 fields?

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.

• ###### counting distinct based on 2 fields?

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

• ###### counting distinct based on 2 fields?

Nothing special,

try

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

• ###### counting distinct based on 2 fields?

Hey Hi,

Use this:

=Aggr(Count(Distinct CandID),Date)

Hope this helps....

Regards

• ###### Re: counting distinct based on 2 fields?

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

• ###### Re: counting distinct based on 2 fields?

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

• ###### Re: counting distinct based on 2 fields?

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

• ###### Re: counting distinct based on 2 fields?

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