topic Re: counting distinct based on 2 fields? in QlikView

counting distinct based on 2 fields?

Mon, 01 Aug 2011 18:15:22 GMT

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

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?

swuehl — Mon, 01 Aug 2011 18:51:25 GMT

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?

Mon, 01 Aug 2011 19:02:06 GMT

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.

counting distinct based on 2 fields?

swuehl — Mon, 01 Aug 2011 19:11:56 GMT

Nothing special,

try

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

counting distinct based on 2 fields?

rahulgupta — Tue, 02 Aug 2011 04:53:11 GMT

Hey Hi,

Use this:

=Aggr(Count(Distinct CandID),Date)

Hope this helps....

Regards

Re: counting distinct based on 2 fields?

Tue, 02 Aug 2011 05:15:37 GMT

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?

Tue, 02 Aug 2011 18:18:14 GMT

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?

swuehl — Tue, 02 Aug 2011 19:18:48 GMT

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?

swuehl — Tue, 02 Aug 2011 19:59:17 GMT

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