Ranking expression without using dimension

[#1]

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

[CELAMBARASAN]

Hi,

     try with this

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

Celambarasan

[#3]

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?

[CELAMBARASAN]

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

[#5]

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

[CELAMBARASAN]

Hi,

     Do you have area dimension here?

Celambarasan

[jagan]

Hi,

Can you attach the sample.

Regards,

jagan.

[CELAMBARASAN · Accepted solution]

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

[#9]

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

Thank you very much!!