Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi I'm using the following to rank a table based on 1 measure and limiting the rows to 25.
=aggr(if(rank(-Q_DCIPU,Q_DCIPU )<=25, Q_DCIPU ),Q_DCIPU )
I want to include more data columns while maintaining the same 25 rows as follows based on the same ranking
Asset | Q_DCIPU | SDWPU | INDEX |
---|---|---|---|
A | 1 | 150 | 0.50 |
B | 1.50 | 10 | 0.01 |
C | 2.25 | 6 | 0.60 |
D | 2.26 | 70 | 0.40 |
E | 5.99 | 99 | 0.06 |
However, when I try something like
=aggr(if(rank(-Q_DCIPU,Q_DCIPU )<=25, SDWPU ), SDWPU )
and
=aggr(if(rank(-Q_DCIPU,Q_DCIPU )<=25, INDEX ), INDEX )
I get a result similar to the following and more rows due to the ranking function as each measure adds another row until the 25 rank values are showing for each measure which is what I don't want
Asset | Q_DCIPU | SDWPU | INDEX |
---|---|---|---|
A | 1 | - | - |
B | 1.50 | 10 | 0.01 |
C | 2.25 | 6 | - |
D | 2.26 | - | - |
E | 5.99 | - | 0.06 |
hope you can help and appreciate it if you can
Maybe I'm missing anything but, why didn't you using the same rank expression for other columns?
Hi Thiago
thanks for the reply
can you write the expression you refer to based on the result I'm looking for so I can try it?
thanks
I was refering this one:
=aggr(if(rank(-Q_DCIPU,Q_DCIPU )<=25, Q_DCIPU ),Q_DCIPU )
but I guess I have a missread here...
Maybe you could try those ones above that you mentioned using this sintax:
=aggr(if(rank(-Q_DCIPU,Q_DCIPU ,3)<=25, INDEX ), INDEX )
Hi Thiago,
I came up with the same result. I tried adding the measure as a dimension and formatting it using the 'money' or the 'num' fuctions. and until I get a better answer this way will satisfy my needs,
thanks