topic Re: Rankings in QlikView

Rankings

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I am currently trying to create a table with the top 3 ranking domains as a dimension in my straight table.

My tables currently look like this:

Ranking Table

Keyword

Domain

Rank

Keyword A

Domain A

Keyword A

Domain B

Keyword A

Domain C

Keyword A

Domain D

Keyword A

Domain E

Keyword B

Domain B

Keyword B

Domain D

Keyword B

Domain A

Keyword Metric Table

Keyword	Traffic	Cost Per Click
Keyword A	50,000	$45
Keyword B	30,000	$7
Keyword C	15,000	$18
Keyword D	18,000	$12

The desired output should be:

Keyword	Traffic	Cost Per Click	#1 Ranking	#2 Ranking	#3 Ranking
Keyword A	50,000	$45	Domain A	Domain B	Domain C
Keyword B	30,000	$7	Domain B	Domain D	Domain A
Keyword C	15,000	$18	Domain C	Domain B	Domain E
Keyword D	18,000	$12	Domain D	Domain E	Domain A

The #1,#2,& #3 ranking column refers back to the rank shown in the ranking table above. Is it possible to show this in a straight table using calculated dimensions?

Re: Rankings

raman_rastogi — Fri, 10 Aug 2018 07:30:27 GMT

Hi Wesly,

Something like this.

Expressions-

FirstSortedValue(Domain,Rank)

FirstSortedValue(Domain,Rank,2)

FirstSortedValue(Domain,Rank,3)

Best

Raman Rastogi

Re: Rankings

Anonymous — Fri, 17 Aug 2018 14:57:47 GMT

Thank you Raman!

I have another question. What if there is a keyword that have domains ranked 1 & 3 but there is no rank 2.

For example:

Keyword C

Domain A

Keyword C

Domain B

Keyword C

Domain C

If I use firstsortedvalue in the table it will show as this:

Keyword	Traffic	Cost Per Click	#1 Ranking	#2 Ranking	#3 Ranking
Keyword C	15,000	$18	Domain A	Domain B	Domain C

Is there a way to show the '#2 Ranking' as null since there is no Rank 2?

Re: Rankings

vishsaggi — Fri, 17 Aug 2018 15:37:23 GMT

may be you need something like this?

If yes share some sample data with all the respective values in the ranking will work on it. The below is just unfinished version of Pivot table.

Re: Rankings

Anonymous — Fri, 17 Aug 2018 15:43:34 GMT

Hi Vishwarath,

I believe that is my desired output in this situation. Was there a specific formula you used to achieve this output?

Re: Rankings

vishsaggi — Fri, 17 Aug 2018 15:47:16 GMT

I have done following changes using Pivot table like add

dims:

Keyword

Traffic

= Aggr(Sum([Cost per Click]), Keyword)

Rank

Exprs:

Firstsortedvalue(Domain, Rank)

Then uncheck supress 0 values in presentation tab. And drag your rank field to top of expr.

Re: Rankings

Anonymous — Fri, 17 Aug 2018 15:58:30 GMT

Thank you! How would you limit the ranks to only show only 1, 2, 3 and exclude the rest?

Re: Rankings

Anonymous — Fri, 17 Aug 2018 16:04:06 GMT

Also is it possible to do this on a straight table as well?

Re: Rankings

vishsaggi — Fri, 17 Aug 2018 16:31:37 GMT

Straight table it is going to be manual right coz you have to create expression based on number of ranking values you have. Not sure in straight table how you do it dynamically. So let me know if Pivot works for you. And based on selection of keyword those ranks will be hidden accordingly.