Count based on another count

[Nive]

Hi All,

I have a requirement where i need to show the count of Id's based on another count in table. PFB my sample data

 i have tried using valuelist in dimension and aggregation in measure.

IF( ValueList('1','2','>2')=1,if(Aggr(count(distinct ID),Name)=1,count(distinct Name)),if(Aggr(count(distinct ID),Name)=2,count(distinct Name)),if(Aggr(count(distinct ID),Name)>2,count(distinct Name)))))

getting null values. 

ID	Name
1	aaa
2	bbb
3	ddd
4	aaa
5	ccc
6	bbb
7	aaa

Output needed:

count of ID's	count of Name
1	2
2	1
>2	1

[sunny_talwar]

Try this

Dimension

Aggr(Count(ID), Name)

Expression

Count(DISTINCT Name)

[sunny_talwar]

To get the exact output, you can change your dimension to this

=Aggr(If(Count(ID) <= 2, Text(Count(ID)), '<2'), Name)

[Nive]

yes, but i wanted the dimension names to be fixed. ( i want the count to be grouped as 1, 2,3, 4-10,>10)

[sunny_talwar]

Did you see my second response to your post?

[sunny_talwar · Accepted solution]

Seems like you deleted your response... but try this

=Aggr(
	If(Count(ID) <= 5, Text(Count(ID)),
	If(Count(ID) < 11, '6-10', '>10'))
, Name)

[Nive]

Yes worked it. got the output.. that's why deleted the last post.

[Nive]

Thanks Sunny.