topic Ranking expression without using dimension in QlikView

Ranking expression without using dimension

Thu, 23 Feb 2012 04:20:34 GMT

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

Ranking expression without using dimension

CELAMBARASAN — Thu, 23 Feb 2012 04:27:55 GMT

Hi,

try with this

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

Celambarasan

Ranking expression without using dimension

Thu, 23 Feb 2012 05:47:44 GMT

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

CELAMBARASAN — Thu, 23 Feb 2012 06:34:58 GMT

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

Ranking expression without using dimension

Thu, 23 Feb 2012 06:59:34 GMT

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

CELAMBARASAN — Thu, 23 Feb 2012 07:01:25 GMT

Hi,

Do you have area dimension here?

Celambarasan

Ranking expression without using dimension

jagan — Thu, 23 Feb 2012 07:07:23 GMT

Hi,

Can you attach the sample.

Regards,

jagan.

Ranking expression without using dimension

CELAMBARASAN — Thu, 23 Feb 2012 07:11:07 GMT

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

Ranking expression without using dimension

Thu, 23 Feb 2012 07:25:20 GMT

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

Thank you very much!!