topic Re: Straight Table row data in Text Box in QlikView

Straight Table row data in Text Box

qlikwiz123 — Wed, 24 Mar 2021 18:48:40 GMT

I have a straight table with Dimension as

Dim: =If(Aggr(Rank(Count(DISTINCT{$<Type={'Sale'}>}(ID))) <= 5,EID),EID)

Expression: Count(DISTINCT{$<Type={'Sale'}>}(ID))

This is to show only top 5 EIDs in the Straight Table that has the most number of IDs

How do I show the top most EID and number of IDs this EID has in a Tex Box?

MayilVahanan — Thu, 25 Mar 2021 06:43:48 GMT

May be try like below

=FirstSortedValue(EID, aggr(rank(Count(DISTINCT{$<Type={'Sale'}>}ID),4),EID))

which gives Top most EID info.

=aggr(if(Rank(Count(DISTINCT{$<Type={'Sale'}>}ID))=1, EID, EID)

------------

Below exp. gives the number of IDs of that EID

Aggr(If(rank(Count(DISTINCT{$<Type={'Sale'}>}ID),4) = 1, Count(DISTINCT{$<Type={'Sale'}>}ID)),EID)

qlikwiz123 — Thu, 25 Mar 2021 12:35:03 GMT

Works as expected. Thank you very much.

qlikwiz123 — Fri, 26 Mar 2021 15:30:42 GMT

Just like FirstSortedValue, do we have anything to show Top 2nd, Top 3rd etc similar to what we show using FirstSortedValue?

MayilVahanan — Sat, 27 Mar 2021 03:48:32 GMT

You can use rank () function or in firstsortedvalue function itself, u can provide rank..

Pls refer in below link