Help required in ranking

[qlikviewforum]

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.

[jagan]

HI,

Try

Rank(TOTAL <CITY>Sum(Sales))

Regards,

Jagan.

[swuehl]

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.

[qlikviewforum]

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

  ))

[sunilkumarqv]

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))))

[qlikviewforum]

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

[qlikviewforum]

Swuehl could you please me on this?

[swuehl · Accepted solution]

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.

[#9]

hi Try this

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

[qlikviewforum]

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.