Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I want to find the rank of a particular column across other columns.
In the below chart, I am want to find the rank of Measure1 among all measures.
Dimension1 | Dimension2 | Mesaure1 | Measure2 | Measure3 | Measure4 | MeasureN | Rank of Measure1 |
A | A1 | 20 | 40 | 50 | 10 | 30 | 4 |
A | A2 | 60 | 100 | 3 | 10 | 30 | 2 |
B | B1 | 40 | 10 | 30 | 130 | 40 | 2 |
B | B2 | 200 | 130 | 50 | 140 | 60 | 1 |
C | C1 | 60 | 140 | 30 | 150 | 70 | 4 |
C | C2 | 90 | 150 | 40 | 50 | 80 | 2 |
Kindly help.
@sunny_talwar @tresesco @swuehl @Gysbert_Wassenaar
Thanks
How exactly are you getting these ranks for Measure 1? Specifically, how are you getting 4 and repeating 2s
Would you like to do it in script or front end?
Hi Kush,
Do you have a solution in script? otherwise front end will also do.
It is going to be the same measure for those fixed columns.
Hi Sunny,
If you see row 1, rank of measure 1 in measure1 to measureN is 4.
Row1:
Mesaure1 Measure2 Measure3 Measure4 MeasureN
20 40 50 10 30
4 2 1 5 3
Similarly, in row 5, rank of measure1 is 4.
Hope this helps.
@sunny_talwar , @Kushal_Chawda Hey guys, suspect the notification issue messed up again on this post, just wanted to let you both know there is new info from the poster.
Cheers,
Brett
@kinjal1645 you can do something like below
For Measure 1 Rank, you need to compare Measure 1 with all other measures and assign 1 value if it is less than the any other measure. So you will get 1 value for each comparison which you can add it to get the rank.
See the expression below for Measure 1 Rank
Here I have used sum(Measure1) & sum(Measure2) as example. You can replace it with actual measure 1 and measure 2 expressions
=rangesum(if(sum(Mesaure1)<sum(Measure2),1,0),
if(sum(Mesaure1)<sum(Measure3),1,0),
if(sum(Mesaure1)<sum(Measure4),1,0),
if(sum(Mesaure1)<sum(MeasureN),1,0),1)
Similarly , you can write the Measure 2 Rank expression as below
=rangesum(if(sum(Measure2)<sum(Mesaure1),1,0),
if(sum(Measure2)<sum(Measure3),1,0),
if(sum(Measure2)<sum(Measure4),1,0),
if(sum(Measure2)<sum(MeasureN),1,0),1)
Similarly you can write the expressions for each of the individual measure. Sorry I am bit lazy so not writing expressions for all measures, but I think you will get a fair idea with these examples