topic Re: how to get Rank in QlikView

how to get Rank

Anonymous — Tue, 23 Jan 2018 06:09:42 GMT

Hi Experts,

I am using Rank function but rank coming but rank coming below

I am using the below logic

=Aggr(Rank(Count(DISTINCT([Meeting Id]))),[Client Name])

Rank
2037-2153
3512-4826
2154-2302
3512-4826
3512-4826
863-877
3512-4826
2473-2684
986-1006
710-722
391-394
1171-1206
1066-1093
2037-2153
2685-3011
2685-3011
1352-1387
2037-2153
3012-3511
2303-2472
682-694
957-985
1936-2036
839-862
752-769
2303-2472
3512-4826

Re: how to get Rank

karthikoffi27se — Tue, 23 Jan 2018 06:30:28 GMT

Hi Nagarjuna,

The count of meeting ID are unique and that's why the values are combined together.

Many Thanks

Karthik

Re: how to get Rank

Thiago_Justen_ — Tue, 23 Jan 2018 09:19:20 GMT

Nagarjuna,

Try this out:

Aggr(Rank(Count(DISTINCT [Meeting Id])),[Client Name])

Cheers

Re: how to get Rank

petter — Tue, 23 Jan 2018 09:48:52 GMT

It behaves as expected and as the documentation claims that it should be.

So what is it you find puzzling or not to your requirements? Could you be specific? What kind of result do you expect?

Re: how to get Rank

Anonymous — Tue, 23 Jan 2018 09:51:35 GMT

Hi Karthik,

After i placing the below logic the rank coming not in Ascending order

=Aggr(Rank(Count(DISTINCT {<[Event Date]={">=$(=Date(vStartDate,'MM/DD/YYYY'))<=$(=Date(vEndDate,'MM/DD/YYYY'))"}, [Event Type]={'Non Deal Management','Conference','Field Visit','Field Trip','Other GCA','IMG - Investor Reverse Inquiry'},[Meeting Type]={'1X1','1X1*','2X1','3X1','GRP','CCL'} >}[Meeting Id]),0,1),[Client Name])

Rank

2000

300

266

222

Like this.

How to sort the Rank

Re: how to get Rank

Thiago_Justen_ — Tue, 23 Jan 2018 10:10:37 GMT

Check this out:

Rank - chart function ‒ QlikView

You may use the function like this:

Rank(YOUR_FIELD, 3)

Number 3 represents the mode parameter. This means:

Cheers

Re: how to get Rank

karthikoffi27se — Tue, 23 Jan 2018 12:02:12 GMT

use sort by expression and copy paste the formula in the sort expression tab and then sort it ascending or descending.

Many Thanks

Karthik

Re: how to get Rank

Anonymous — Tue, 23 Jan 2018 12:07:15 GMT

Hi Karthik

Tried but no change in my Rank

Rank is a Calculated Dimension.

Thanks,

Nagarjuna

Re: how to get Rank

Thiago_Justen_ — Tue, 23 Jan 2018 12:10:45 GMT

Try this one:

Aggr(Rank(Count(DISTINCT {<[Event Date]={">=$(=Date(vStartDate,'MM/DD/YYYY'))<=$(=Date(vEndDate,'MM/DD/YYYY'))"}, [Event Type]={'Non Deal Management','Conference','Field Visit','Field Trip','Other GCA','IMG - Investor Reverse Inquiry'},[Meeting Type]={'1X1','1X1*','2X1','3X1','GRP','CCL'} >}[Meeting Id]),3),[Client Name])

Cheers

Re: how to get Rank

Anonymous — Tue, 23 Jan 2018 12:31:35 GMT

Hi Thiago,

After Trying Below expression output like below


RAnk
31
319-324
19
2462-3054
782-804
917-950
60-61
139-141
664-686
30
589-597
363-373
54
173

Thanks,

Nagarjuna

Re: how to get Rank

Thiago_Justen_ — Tue, 23 Jan 2018 12:38:22 GMT

Could you please try to put same expression into sort tab?

Cheers

Re: how to get Rank

Anonymous — Tue, 23 Jan 2018 12:42:13 GMT

Hi Thiago,

I put same expression into sort tab getting same above output

Re: how to get Rank

Thiago_Justen_ — Tue, 23 Jan 2018 12:55:43 GMT

Could you please send us an example app?

Re: how to get Rank

petter — Tue, 23 Jan 2018 13:25:21 GMT

Your expression works perfectly well and sorts according to rank in a normal table for me with my test data. Can you tell exactly what kind of table you are using the expression in and the dimension(s)?

The expression should not be in a calculated dimension but in a measure by the way.

Re: how to get Rank

Anonymous — Wed, 24 Jan 2018 05:40:32 GMT

Hi Petter ,

That expression working fine but that rank coming miss order like below

Rank

122

Like that

How to get sort is rank is question in Pivot table

Re: how to get Rank

petter — Wed, 24 Jan 2018 08:09:41 GMT

In that case you can specify the sort order by using Count(DISTINCT [Meeting ID]) in descending order: