RANK TABLE

[eva_maria]

Hi!

From the table that i put in my question file, i need to create tables like that, where numbers are the ranking depending on the average value. If the average is the same, the rank will be the same.

Thanks for your help.

table 1 only with pto
pto	Q1_E	Q2_E	Q3_E	Q4_E	Q1_J	Q2_J	Q3_J	Q4_J
COM	1	2	1	1	3	2	1	2
DIR	2	3	2	1	2	3	1	2
JCDTOS	1	1	2	3	2	1	3	4
SUBD	2	2	1	3	2	2	1	2
table 2 with pto and zone
pto_zon	Q1_E	Q2_E	Q3_E	Q4_E	Q1_J	Q2_J	Q3_J	Q4_J
COM_G	1	2	1	1	1	2	1	2
COM_S	1	1	1	1	2	1	1	1
DIR_G	2	2	1	1	2	3	1	2
DIR_M	1	1	1	1	2	2	1	2
DIR_S	2	3	4	1	1	1	1	1

[eva_maria]

Sorry i've attached the wrong file.

This is the real data table.

Regards

[neelamsaroha157]

You want your data to look like this -

If yes, then try rank function.

[eva_maria]

Hi Neelam!

Thanks for your reply.

Using rank fuction as you do in your table, sets the rankin by columns (Pto field) and I need it by rows and making difference between the questions for the employee (_E) and their boss (_J).

As you can see in my question file, for Pto COM, depending on the avg value of the employee answers, Q1_E,Q3_E and Q4_E have the same value so the rank has to be 1. Question Q2_E average is lower than the other so rank has to be 2.

For the boss, Q3_J has the highest average so rank is 1. Q2_J and Q4_J  are next so rank for both is 2. Finally Q_J has the lower average so rank is 3.

Regards

[Frank_Hartmann]

Is this what you are looking for?

[eva_maria]

Hi Frank!

Thanks for your reply, is almost perfect. just missing to calculate separately the ranking of the questions of the employee (Q*_E) of those of the boss (Q*_J).

Regards

[Frank_Hartmann]

like this?

[eva_maria]

Perfect!!

How did you do it??

[Frank_Hartmann · Accepted solution]

pivot table:

dim1 = pto

dim2 = valuelist('Q1_E','Q2_E','Q3_E','Q4_E','Q1_J','Q2_J','Q3_J','Q4_J')

Expression =

if(match( valuelist('Q1_E','Q2_E','Q3_E','Q4_E','Q1_J','Q2_J','Q3_J','Q4_J'),'Q1_E','Q2_E','Q3_E','Q4_E'),

hrank(pick(match(valuelist('Q1_E','Q2_E','Q3_E','Q4_E','Q1_J','Q2_J','Q3_J','Q4_J'),'Q1_E','Q2_E','Q3_E','Q4_E'),

    avg(E_I1),avg(E_I2),avg(E_I3),avg(E_I4),)),

hrank(pick(match(valuelist('Q1_E','Q2_E','Q3_E','Q4_E','Q1_J','Q2_J','Q3_J','Q4_J'),'Q1_J','Q2_J','Q3_J','Q4_J'),

    avg(J_I1),avg(J_I2),avg(J_I3),avg(J_I4),)))

then drag the second dimension to the upper right corner untill you see a blue horizontal line

[eva_maria]

Thanks for your invaluable help and for your time!!

Regards