How to make Unique Number?

[#1]

Hi Experts,

i have a request that i have to get the Unique Numbers from given numbers

Sample Given Table:

Id	Months	Names	Visits
0021	JAN	Jack	340
9045	JAN	John	248
4561	JAN	Leslie	3

OUTPUT:

Id	Months	Header 3	Visits	Unique Visits
	JAN	Jack	340	1
	JAN	John	248	1
	JAN	Leslie	3	1

So each Month Unique Visits Increments by 1

E.g JAN = 1

     FEB = 1

     MAR = 1

     APR = 1

and so on, so in a year it should be 12 Visits.

[Peter_Cammaert]

I think I get it. You want the number of unique visits per month for all selected months?

Then how about:

=sum(aggr(count(DISTINCT CustomerCodeName), SalesMonth))

[#3]

Yes,

How can i Tackle it, so that every month is equal to 1 if there's Visit if not make it Zero.

Regards.

[Peter_Cammaert]

Note that when you add dimensions to your straight table, you may have to add them to the aggr() function as well.

[Peter_Cammaert]

That depends on whether or not Month is a dimension in your table.

My first suggestion =count(DISTINCT Names) would serve you well in a table with just Month as a dimension. The second one should do in any other table.

[#6]

Hi,

below is sample answer i got:

Month	SalesRepID	Distinct Reps	Distinct SalesDate	Total Visits	Distinct Unique Visits
Apr	0032	1	1	12	1
Apr	0043	1	1	8	0
Apr	0082		1	13	-
Apr	0065		1	13	-

On Distinct Unique Visits it should be only 1 for all Rows.

I can't get this right, its giving me a headache.

=aggr(Count(DISTINCT SalesDate),SalesDate)

[petter]

AutoNumber is a function that is available only in a load script. So you could pre-calculate it in a Load Script.

If you want it dynamically assigned you will have to do  Count(DISTINCT Id) in a chart:

Dimensions:   Id, Months, Names

Expressions: Sum(Visits), Count(DISTINCT Id)

For the Count(DISTINCT Id) in a Straight Table you will have to on the Expressions Tab select Sum of Rows for Total Mode for that particular expression.

In a Pivot Table you will have to have a slightly different expression for the last expression:

If( RowNo() = 0 , Count(NoOfRows()) - 1 , Count(DISTINCT Id) )

[petter]