Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a straight table which have multiple groups and each group has many sub groups within them. Each group has 2 scores associated with it at the group level Score1_GRP and Score2_GRP. Similarly each sub group has 2 scores associated with it SCR1_IND and SCR2_IND.
Group | Individual | Score1_GRP | Score2_GRP | SCR1_IND | SCR2_IND |
---|---|---|---|---|---|
A | IA | 5 | 4 | 3 | 5 |
A | IB | 5 | 4 | 5 | 4 |
A | IC | 5 | 4 | 3 | 3 |
B | ID | 3 | 4 | 4 | 4 |
B | IF | 3 | 4 | 2 | 2 |
C | IG | 5 | 5 | 5 | 5 |
C | IH | 5 | 5 | 3 | 4 |
C | IK | 5 | 5 | 4 | 4 |
I need the 2 sorts on this table, the first one being based on the combined sum of the group score(Score1_GRP and Score2_GRP) with the highest coming first and second, the sorting of the sub groups based on the combined sum of individual scores(SCR1_IND and SCR2_IND) as follows,
Group | Individual | Score1_GRP | Score2_GRP | SCR1_IND | SCR2_IND |
---|---|---|---|---|---|
C | IG | 5 | 5 | 5 | 5 |
C | IK | 5 | 5 | 4 | 4 |
C | IH | 5 | 5 | 3 | 4 |
A | IB | 5 | 4 | 5 | 4 |
A | IA | 5 | 4 | 3 | 5 |
A | IC | 5 | 4 | 3 | 3 |
B | ID | 3 | 4 | 4 | 4 |
B | IF | 3 | 4 | 2 | 2 |
Sum(Score1_GRP) + Sum(Score2_GRP) + Sum(SCR1_IND) + Sum(SCR2_IND)
Sort by Expression. Place it in all dimension, click descending. Uncheck other sort options
If Score1_GRP and Score2_GRP are similar and add up to same number between different groups, i dont think this formula would work.
Provide sample and expected output. You may have to use set analysis, do it in script, and may be able to use autonumber to come up with a solution.