topic Re: display only top 20 -20 in a table based on a expression in QlikView

display only top 20 -20 in a table based on a expression

nishanthlm — Fri, 31 Mar 2017 12:12:59 GMT

Hi all,

I have a requirement to show top 10 accounts by sales in a table.(Done using Dimension limits)

I have to show the top 11-20 in another table.

I would need these 2 tables as i have to pull them into 2 different slides using N printing.

Could any one help me on how to show top 11-20 account based on sales in a seperate table in qlikview???

sarvesh — Fri, 31 Mar 2017 12:17:26 GMT

Can you share your data with me.

sunny_talwar — Fri, 31 Mar 2017 12:18:09 GMT

May be using Rank() function... what is your expression and what all dimensions do you have?

vinieme12 — Fri, 31 Mar 2017 13:13:36 GMT

Remove Dimension Limits from both Tables

Add Set analysis to expression as example

for 1 -10

sum({<Account={"=rank(sum(Sales),1,1)<=10"}>} Sales)

for 11-20

sum({<Account={"=rank(sum(Sales),1,1)>10"}>*<Account= {"=rank(sum(Sales),1,1)<=20"}>} Sales)

sunny_talwar — Fri, 31 Mar 2017 13:34:36 GMT

I would handle this a slight different way

Instead of this

sum({<Account={"=rank(sum(Sales),1,1)>10"}>*<Account= {"=rank(sum(Sales),1,1)<=20"}>} Sales)

I would use this

sum({<Account={"=Rank(Sum(Sales),1,1) > 10 and Rank(sum(Sales),1,1) <= 20"}>} Sales)

Result wise no difference, but I feel its easier to read

nishanthlm — Fri, 31 Mar 2017 13:41:24 GMT

Thankyou...

I have achieved with below expression

if(Rank(Sum([Field]))<=30 and Rank(Sum([field]))>20, sum([field]))