topic Re: Ranking in QlikView

Ranking

Fri, 14 Feb 2014 10:36:20 GMT

Good Day Qlik-Nation,

I am trying to rank the attached spreadsheet on my dashboard and I am using the following expression however it does no seem to be ranking correctly if i try to add another dimension...Please help...

(Gender='Male', Div(Index('-' & Concat(DISTINCT TOTAL num(aggr(rank(Score,1,1),Name), '0000'), '-', -Score),num(rank(max(Score),1,1),'0000' ) ),5)+1)

and/or

if(Gender='Male', num(rank(Score)))

Re: Ranking

datanibbler — Fri, 14 Feb 2014 10:46:00 GMT

Hi Sanjeev,

and by what do you want to rank them?

Understanding a formula without knowing what it's supposed to be doing is a bit hard - once you know the desired outcome, thinking out ways how to get there is much easier - likewise for supporters - it's a lot easier to tell why your formula isn't doing what you want when you tell what you actually want your formula to do 😉

Best regards,

DataNibbler

Re: Ranking

Fri, 14 Feb 2014 10:58:36 GMT

Hi DataNibbler,

I want to rank them by Score, Age, Gender and Area.

Regards,

Sanjeev

Re: Ranking

datanibbler — Fri, 14 Feb 2014 11:19:24 GMT

Hi Sanjeev,

in that order? What keeps you then from just inserting an "ORDER BY" in the script?

<=> That can only be done in a RESIDENT LOAD - but that doesn't add much runtime once your data is loaded.

=> The syntax would then be

LOAD

...

RESIDENT [tablename from first LOAD]

ORDER BY Score, Age, Gender, Area

;

By default, unless you specify it otherwise, all the fields are ordered (first by Score, then by Age etc.) in ascending order - unless you type "desc" behind each field where it's applicable.

HTH

Best regards,

DataNibbler

Re: Ranking

Fri, 14 Feb 2014 11:24:24 GMT

Thanks DataNibbler. I will try that.

Re: Ranking

vikasmahajan — Fri, 14 Feb 2014 11:27:00 GMT

Try this you need to use AGGR with Rank

Aggr(num( rank( sum(Score ), 4)), Age, Gender and Area.)

Vikas

Re: Ranking

vikasmahajan — Fri, 14 Feb 2014 11:33:05 GMT

pfa attached

Vikas