topic Re: Rank Function ? in QlikView

Rank Function ?

qlikuser222 — Sat, 16 Nov 2024 20:59:48 GMT

Hi Everyone,

I have a requirement ,how can we use rank function to sort the column in straight table alphabetically A to Z( but suppressing where expressions amounts are 0 and null) and have a "* "in just first row?

PFA which explains the requirement clearly.

Thanks

Re: Rank Function ?

sunny_talwar — Wed, 24 Apr 2019 18:38:56 GMT

Is this what you are looking to get?

Name	Amt1	Amt2
*
BAB	10	20
BCC	7	9
CCC	23	9
DDD	100	200

Re: Rank Function ?

qlikuser222 — Wed, 24 Apr 2019 18:42:52 GMT

Thanks

It should be like below

Name Amt1 Amt2 4thcolumn

BAB 10 20 *

BCC 7 9

CCC 23 9

DDD 100 200

Thanks

Re: Rank Function ?

sunny_talwar — Wed, 24 Apr 2019 18:59:00 GMT

This?

Re: Rank Function ?

qlikuser222 — Tue, 07 May 2019 17:48:25 GMT

Hi,

Thanks alot for earlier solution.

Can you please help me to understand how to make it work we introduce one more dimension in the table

PFA for more details

Thanks

Re: Rank Function ?

sunny_talwar — Tue, 07 May 2019 17:51:46 GMT

Would you want to see 2 * or 1 * and where?

Re: Rank Function ?

qlikuser222 — Tue, 07 May 2019 18:01:08 GMT

Thanks

BAB has Market and Sales and i want to see * on the first row ( just 1)

example first row

BAB Markets 10 20 *

Re: Rank Function ?

sunny_talwar — Tue, 07 May 2019 18:27:52 GMT

This should do it

If(Num(Rank(TOTAL Only({<Name = {"=Sum(Amt1) + Sum(Amt2) > 0"}>}Name&Task), 4, 1)) = Count(DISTINCT TOTAL {<Name = {"=Sum(Amt1) + Sum(Amt2) > 0"}>} Name&Task), '*')

Re: Rank Function ?

qlikuser222 — Tue, 07 May 2019 18:36:20 GMT

Thanks its working.

Can you please tell me why you used 4,1?

Re: Rank Function ?

sunny_talwar — Tue, 07 May 2019 18:38:11 GMT

1 is not required, but 4 is used to give it distinct rank... that might not be needed as well because I added Task to the Only() function

Re: Rank Function ?

qlikuser222 — Wed, 08 May 2019 14:19:40 GMT

Thanks for helping me understand.

Is it possible to make * sign always stays for first row? ( BAB - Market)

Like when we select BCC - Market row or CCC - Sales or DDD - Sales the * should go away from the table, it should always stays on BAB - Market?

Thanks

Re: Rank Function ?

sunny_talwar — Wed, 08 May 2019 14:24:56 GMT

Why not create it in the script and how did you decide it to be BAB - Market? Can this change based on the new data that comes in?

Re: Rank Function ?

qlikuser222 — Wed, 08 May 2019 14:30:30 GMT

Hi,

Yes it can change, * sign should always be on the first row,

like if i get BAA - Market with some AMt values then * should always stay on that row instead of BAB - Market

Thanks

Re: Rank Function ?

sunny_talwar — Wed, 08 May 2019 14:32:10 GMT

But the first row can change based on selection, no? if it is first row based on overall data... I would still say do it in script