FirstSortedValue for first n values

[ben2r]

Hi all, 

I am trying to create a visualisation which shows the top n words mentioned in call records across different teams. 

I have a ranked list of words by team and have created a pivot table using FirstSortedValue but as far as I can see I need to create an expression for each word value by changing the rank argument from 1 - 10. 

This is ok for a few words but if I need to expand it will get complicated. It is additionally challenging to define common colors to the words as I need to pass the script below to each 'Background Color' field and modify it for the word rank.

FirstSortedValue(
       TAG_TOPICS_SPLIT,  //this is the word list
       -AGGR(COUNT(DISTINCT CALL_ID),TAG_TOPICS_SPLIT,TAG_RGN), //count of # occurrences by region/team
       1) //return nth value (1st in this case)

I am building up a table that looks like this: 

Team	A	B	C
1	Apple	Plum	Raspberry
2	Banana	Peach	Plum
3	Strawberry	Pear	Strawberry
4	Peach	Banana	Pear
5	Plum	Strawberry	Peach
6	Pear	Apple	Banana
7	Raspberry	Raspberry	Apple
...

Is there a way to simplify this? For example - I tried using a variable in the rank function with values 1-10 but it does not seem to accept this. 

Appreciate any advice out there on this. 

[sunny_talwar]

What exactly are you intending to show and where?

[ben2r]

The table I laid out above is currently a pivot table with region as the dimension and the expressions are the firstsortedvalue formulas x10 (with rank set at 1,2,3 etc)

I have an input straight table of data which looks like this: 

Word	Count	Team
Apple	10	A
Banana	8	A
Strawberry	5	A
Peach	3	A
Plum	2	A
Pear	1	A
Raspberry	0	A
Apple	5	B
Banana	7	B
Strawberry	6	B
Peach	11	B
Plum	15	B
Pear	9	B
Raspberry	4	B
Apple	1	C
Banana	2	C
Strawberry	6	C
Peach	3	C
Plum	10	C
Pear	5	C
Raspberry	12	C

[sunny_talwar · Accepted solution]

Are you looking for something like this? I choose to show top 5, but you can change that to 10 or 100 based on your req or variable input from user

[ben2r]

Thanks for that Sunny - that helps. It is a different approach to where I was going which I think maybe helps explain where I was failing. 

In reality my data model was a little more complicated so I had to make some changes - sharing below in case this helps anyone else out: 

In your dimension formula here: 

Aggr(If(Rank(Count) <= 5, Rank(Count)), Team, Word)

Count would be an aggregation like this:  AGGR(Count(Distinct Calls),Team,Word)

Then as the measure I use Concat(Distinct Word) to get the unique text value.

Thanks as always Sunny!