Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
Not applicable

Ranking expression without using dimension

Hi all,

I would need your help with a problem I'm currently trying to solve (if it can be solved at all)

I'm using geoqlik to show maps, the problem is, that I cant use a dimension in their forground analysis.

I would like to know if it is possible to solve the following problem without a dimension or by putting a dimension into an expression:

In order to color area, I would need to get the rank for Brand XY based on Sum(Sales) in each area.

I have the solution in a basic table where I can sort the rank and it looks like this:

aggr(rank(sum({<CYTD={1}>}Sales)),Area,Brand)  but here I have a calculated dimension: if(Brand='xy', Brand)

then it shows me

Area      Rank

xyz1          1

xyz2          1

xyz3          1

xyz4          2

...

So would it be possible to bring this Expression and the calculated Dimension together into an Expression?

You genious ideas are much appreciated..

Tom

1 Solution

Accepted Solutions

Ranking expression without using dimension

Hi,   

     If you have area dimension then try this without area in set analysis like below.

     =Sum({<CYTD={1},Brand={'xy'}>}aggr(Rank(Sum({<CYTD={1}>}TotalSales)),Area,Brand))

Celambarasan

8 Replies

Ranking expression without using dimension

Hi,

     try with this

     =aggr(rank(sum({<CYTD={1},Brand={'xy'}>}Sales)),Area)

Celambarasan

Not applicable

Ranking expression without using dimension

Dear Calambarasan,

also a very helpful expression for me but with this expression I get the complete area ranking...basically area 1-10, but what I was looking for is the rank in EACH area based on the sales.

Meaning, look at all the sales in Area 1, where does Brand 'xy' stands? 

So its the rank of Brand 'xy' in each of the areas what I am looking for..

Do you have any suggestion how to change the expression?

Ranking expression without using dimension

Hi,

     If you able to get the area you can use the below.

     =Sum({<CYTD={1},Brand={'xy'},Area={somearea}>}aggr(Rank(Sum({<CYTD={1}>}TotalSales)),Area,Brand))

somearea is any one area you should mention and it will return the rank of the brand in that area.

Hope it helps

Celambarasan

Not applicable

Ranking expression without using dimension

Dear Celambarasan,

thats another even more interesting expression, which will also partly help me when I want to have a closer look into 1 or 2 areas.

But we have like 40 areas with 200 subareas and I would like to show on a map where brand 'xy' is rank 1 or rank 2, so your approach here is very interesting but not really suitable for me if I need to look at all the areas.

Is there any other way I would be able to show this without specifying the area?

Thank you very much for your help....

Ranking expression without using dimension

Hi,

     Do you have area dimension here?

Celambarasan

MVP & Luminary
MVP & Luminary

Ranking expression without using dimension

Hi,

Can you attach the sample.

Regards,

jagan.

Ranking expression without using dimension

Hi,   

     If you have area dimension then try this without area in set analysis like below.

     =Sum({<CYTD={1},Brand={'xy'}>}aggr(Rank(Sum({<CYTD={1}>}TotalSales)),Area,Brand))

Celambarasan

Not applicable

Ranking expression without using dimension

you are a genius! That is exactly what I was looking for!

Thank you very much!!