topic Re: FirstSortedValue for first n values in QlikView

FirstSortedValue for first n values

ben2r — Sat, 16 Nov 2024 02:42:26 GMT

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.

Re: FirstSortedValue for first n values

sunny_talwar — Mon, 19 Aug 2019 14:45:44 GMT

What exactly are you intending to show and where?

Re: FirstSortedValue for first n values

ben2r — Mon, 19 Aug 2019 14:52:14 GMT

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

Re: FirstSortedValue for first n values

sunny_talwar — Mon, 19 Aug 2019 15:32:03 GMT

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

Re: FirstSortedValue for first n values

ben2r — Mon, 19 Aug 2019 16:25:52 GMT

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!