Count distinct per Date

[#1]

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

[robert_mika]

Try like this:

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

[DavidFoster1]

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

[jagan]

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.