Ranking over dimension not being part of pivot

[hugmarcel]

Hi

having data:

DATA:

LOAD * INLINE [

emp, product, turnover

EMP1, PRODUCT 1, 200

EMP1, PRODUCT 2, 200

EMP2, PRODUCT 1, 100

EMP2, PRODUCT 2, 500

EMP3, PRODUCT 1, 300

EMP4, PRODUCT 3, 400

];

I calculate the rank of sum(turnover) per person and product. E.g. result is:

product            emp sum(turnover) rank(sum(turnover))

PRODUCT 1 EMP1      200                      2

PRODUCT 1 EMP2      100                      3

PRODUCT 1 EMP3      300                      1

PRODUCT 2 EMP1      200                      2

PRODUCT 2 EMP2      500                      1

PRODUCT 3 EMP4      400                      1

Now, when I remove the employee as dimension member of the pivot, the ranking per person is lost. How can i maintain the original ranking order

when I just select a single employee in a list box? E.g., having EMP1 selected, pivot result should be:

product            sum(turnover) rank(sum(turnover))

PRODUCT 1      200                      2

PRODUCT 2      200                      2

Thank you! - Marcel

[lironbaram]

have a look at this example

i didnt remove the emp from the table but it keep the rank of the employee

hope it helps you

[hugmarcel · Accepted solution]

Looks very nice, however, I'll first have to understand the formulas in order to understand the whole example .

Now without keeping the EMP as a dimension, it would be even more genius...

[jagan]

Hi,

Hats off to Liron for his expression.  You are genious Liron.

You can change the EMP dimension by following the below steps.

Change Pivot Table to Straight table and hide EMP(aggr(only({<emp=P(emp)>}emp),emp)) dimension in

Chart Properties -> Presentation Tab -> Select the EMP column and choose  Hide Column option

This hides the EMP column as you expects.  Hope this helps you.

Regards,

Jagan.

[lironbaram]

yes i also thaought on jagan idea

but to make it complete the solution

i clone the table and changed it to stright table using jagan idea

i insert a layout show condition for the stragight tabe:

getposiblecount(emp)=1

and for the pivot table

getposiblecount(emp)>1

see attach example

[hugmarcel]

Phantastic! Exactly what I wanted!

My last wish : Put all the rankings (1., ... n) into a listbox. When ranking = 1 is selected, of course only rows having rank = 1 shall be displayed in the pivot.

Thanks a lot for your help!

Marcel