topic Help required in ranking in QlikView

Help required in ranking

qlikviewforum — Fri, 19 Feb 2016 06:35:16 GMT

Hi,

We have already developed the below chart which show Top 5 sales of particular "City". Now user wants to have one more column called Rank. Is there is anyway doing this easily in UI instead adding it script level which is difficult to maintain due to complex design. Could you please help me on how to achieve this in the UI? Is the requirement is not clear please let me know.

Re: Help required in ranking

jagan — Fri, 19 Feb 2016 06:42:53 GMT

HI,

Try

Rank(TOTAL <CITY>Sum(Sales))

Regards,

Jagan.

Re: Help required in ranking

swuehl — Fri, 19 Feb 2016 06:45:03 GMT

You can use Rank() function in the UI:

Create an expression

=Rank(Sum( Sales))

This should be reset at so called column segments, so it's depending on the dimensions used and order of dimension, I assumed dimensions like

City

SALES_ID

If you limited to top 5 using set analysis, you also need to add the set expresssion to the above Sum() function.

Re: Help required in ranking

qlikviewforum — Fri, 19 Feb 2016 07:03:24 GMT

Hi Swuehl and Jagan,

Tried what you have said in my chart but it is not giving the expected result. Below is the code I am using in the "SALES" column. Please let me know what is that I should be using for my RANK column. Please help!

sum({<$(e_DateRange) >} aggr(if(

rank(

(

sum({<$(e_DateRange), DATA_SRC={'SALES'}>}fabs(SALES))

),4

)<=5,

sum({<$(e_DateRange), DATA_SRC={'SALES'}>}fabs(SALES))

)

,CITY_ID, KEY_PERIOD_SALES_ID

))

Re: Help required in ranking

sunilkumarqv — Fri, 19 Feb 2016 07:07:57 GMT

Perhaps try this expression :

If(Rank(sum(aggr(sum({<$(e_DateRange), DATA_SRC={'SALES'}>}fabs(SALES)),City),4)<=5,

rank(sum({<$(e_DateRange), DATA_SRC={'SALES'}>}fabs(SALES))))

Re: Help required in ranking

qlikviewforum — Fri, 19 Feb 2016 07:13:41 GMT

Thanks Sunil I have already tried that but it didnt work!

Re: Help required in ranking

qlikviewforum — Fri, 19 Feb 2016 07:59:39 GMT

Swuehl could you please me on this?

Re: Help required in ranking

swuehl — Fri, 19 Feb 2016 08:36:14 GMT

It's easier to help with a good understanding of your data model and expression context (e.g. chart dimensions used), so if possible, please upload a small sample QVW.

Maybe you can use something like

Only({<$(e_DateRange) >} aggr(if(

rank(

(

sum({<$(e_DateRange), DATA_SRC={'SALES'}>}fabs(SALES))

),4

)<=5,

rank(

sum({<$(e_DateRange), DATA_SRC={'SALES'}>}fabs(SALES))

,4)

)

,CITY_ID, KEY_PERIOD_SALES_ID

))

There might be easier solutions, but it's hard to understand your setting without knowing some more details.

Re: Help required in ranking

Fri, 19 Feb 2016 09:06:56 GMT

hi Try this

=Rank(sum(Sales),0,1)

Re: Help required in ranking

qlikviewforum — Fri, 19 Feb 2016 13:38:37 GMT

Hi Swuehl,

Sorry for late response, I got stuck with some issue. Looks to be good I guess. But I will try once and update you on this.