Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
ben2r
New Contributor II

FirstSortedValue for first n values

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: 

TeamABC
1ApplePlumRaspberry
2BananaPeachPlum
3StrawberryPearStrawberry
4PeachBananaPear
5PlumStrawberryPeach
6PearAppleBanana
7RaspberryRaspberryApple
...   

 

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. 

1 Solution

Accepted Solutions

Re: FirstSortedValue for first n values

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

image.png

View solution in original post

4 Replies

Re: FirstSortedValue for first n values

What exactly are you intending to show and where?

ben2r
New Contributor II

Re: FirstSortedValue for first n values

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: 

WordCountTeam
Apple10A
Banana8A
Strawberry5A
Peach3A
Plum2A
Pear1A
Raspberry0A
Apple5B
Banana7B
Strawberry6B
Peach11B
Plum15B
Pear9B
Raspberry4B
Apple1C
Banana2C
Strawberry6C
Peach3C
Plum10C
Pear5C
Raspberry12C

Re: FirstSortedValue for first n values

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

image.png

View solution in original post

ben2r
New Contributor II

Re: FirstSortedValue for first n values

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!