Find Position in Sorted List

[bdiamante]

I am trying to find a way to calculate a specific position index of a dimension for a given expression.

For example: If we have the chart expression 'sum(sales)' and the chart dimension 'Region', how could I find the position of a specific region in the result? Let's say I am interested in the 'Northeast' region and I have the following results from the expression/dimension above:

Southeast: 120,000

Southwest: 105,000

Midwest: 78,000

Northeast: 72,000

West: 34,000

I would like to display in a text box the position of the 'Northeast' region. So my text box would contain the number 4, since 'Northeast' is 4th in sorted order.

Even though this seems trivial, I cannot find a built-in function that does this and I've been racking my brain but cannot come up with a solution. Any ideas?

[nagaiank]

Did you consider using Rank() function? If you add the column Rank(Sum(sales)), it will display 4 for your data.

[mambi]

for : Northeast you can use :

=only({<col1={'Northeast'}>} aggr((rank(sum((col2)))),col1))

[VishalWaghole]

Hi Brian,

Please find attached file, it will help you.

- Regards,

Vishal Waghole

[MK_QSL · Accepted solution]

This can be achieved by below two expressions in TEXT BOX.

Please check enclosed file...

=Aggr(IF(Region = GetFieldSelections(Region),Rank(SUM({1}sales))),Region)

or

=Aggr(Rank(SUM({1}sales)),Region)

[bdiamante]

This is exactly what I was looking for thanks!