Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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)
Try this
Dimension
Aggr(Count(ID), Name)
Expression
Count(DISTINCT Name)
To get the exact output, you can change your dimension to this
=Aggr(If(Count(ID) <= 2, Text(Count(ID)), '<2'), Name)
yes, but i wanted the dimension names to be fixed. ( i want the count to be grouped as 1, 2,3, 4-10,>10)
Did you see my second response to your post?
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)
Yes worked it. got the output.. that's why deleted the last post.