topic Re: Count distinct per Date in QlikView

Count distinct per Date

Tue, 07 Apr 2015 08:50:47 GMT

Hi Guys

This looks like a simple one - but i just cant get around it.

Please see below, i want to able count the distinct number of Agents per Date.

Date	Agent
01-Apr	A
01-Apr	B
02-Apr	A
02-Apr	B
02-Apr	B
03-Apr	A
03-Apr	B
03-Apr	C
03-Apr	A
03-Apr	B
03-Apr	C
03-Apr	A

So the answer should be:

Date	Count of Agents
01-Apr	2
02-Apr	2
03-Apr	3

Further to that, if the user selects a month then it counts the distinct number of Agents for that month.

If the user selects a year then it counts the distinct number of Agents for that year.

Hope this makes sense.

Any help would be appreciated.

Regards,

Magen

Re: Count distinct per Date

prieper — Tue, 07 Apr 2015 09:10:53 GMT

If the date/month/year is the dimension, simple formula COUNT(DISTINCT Agent) should do?

Peter

Re: Count distinct per Date

PrashantSangle — Tue, 07 Apr 2015 09:12:31 GMT

Hi,

Expression will depends on dimension

if you have date as dimension then by simple

Count(distinct Agent)

will work.

Regards

Re: Count distinct per Date

robert_mika — Tue, 07 Apr 2015 09:17:17 GMT

Please see attached

Re: Count distinct per Date

alexandros17 — Tue, 07 Apr 2015 09:23:52 GMT

this is my example, let me know

Re: Count distinct per Date

jagan — Tue, 07 Apr 2015 09:31:44 GMT

Hi,

Simple Count(distinct Agent) works, if this is not your expectation explain bit more.

Regards,

Jagan.

Re: Count distinct per Date

buzzy996 — Tue, 07 Apr 2015 09:49:10 GMT

try this,

1.Just load ur data as it's.

2. Set Dimesion as Date

3. Set expression as Count(Distinct Agent)

Re: Count distinct per Date

Tue, 07 Apr 2015 10:53:44 GMT

Hi guys

This is part of a bigger calculation.

So it something like the sum(Sales)/count (distinct(agents) per date .. if that makes sense

My dimension is not going to be a date, it is City, State etc ..

Regards,

Magen

Re: Count distinct per Date

jagan — Tue, 07 Apr 2015 10:55:37 GMT

Hi,

Try like this

Sum(Aggr(count (distinct agents), Date) )

Regards,

Jagan.

Re: Count distinct per Date

Tue, 07 Apr 2015 11:15:54 GMT

HI Jagan

That sounds about right but on its own (eg. in a text box) but when i place in my expression then it doesnt calculate for all of my dimensions.

Do i have to place in the dimension as part of my aggr calculation?

Regards,

Magen

Re: Count distinct per Date

robert_mika — Tue, 07 Apr 2015 11:24:11 GMT

Try like this:

Sum(Aggr(count (distinct agents), Date, dim1,dim,2,.....) )

Re: Count distinct per Date

DavidFoster1 — Tue, 07 Apr 2015 11:39:25 GMT

The Aggr statement is used to set the context for the calculation. It looks like you are trying to calculate daily sales per 'head' and then use that in further calculations. In that case you need to include the Sales calculation inside the aggr as well.

TRY:

AVG(AGGR(SUM(SALES)/COUNT(DISTINCT AGENTS),Date))

Re: Count distinct per Date

jagan — Wed, 08 Apr 2015 02:08:08 GMT

Hi,

Try like this as Mika suggested, you can add additional parameters to Aggr which you are using in your chart

Sum(Aggr(count (distinct agents), Date, dim1,dim,2,.....) )

Regards,

Jagan.