Skip to Content
1

Is there a RANK() function available at BOC level?

Jan 09, 2017 at 06:24 AM

92

avatar image

Hi,

Suppose we have a set of dimensions and measure displayed against it. IF we wish to rank the rows accordingly & display it. Can someone guide if RANK function is available at BOC level or how to implement the same?

Regards,

Kiran Shenvi

10 |10000 characters needed characters left characters exceeded

I'm trying to follow where you want this, as there are options to rank covered in the PDF of the SAP Help, page 52 https://cp.hana.ondemand.com/dps/d/preview/06d2c3deddfc425e920f2448e4f1e654/release/en-US/frameset.htm

0

Hi Tammy,

Thanks for your reply!

I wish to calculate rank using a function in a new calculated measure and show it as part of output and not filter as TopN in the visualization. I tried searching it in your link but its a different use case.

Regards,

Kiran Shenvi

0
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
John Leggio
Jan 11, 2017 at 07:41 PM
1

Hi Kiran,

Sorry there is no RANK function in regards to calculated measures.

The story calculation editor only supports the following aggregation functions:

Thanks,


Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hi John,

Thanks for your reply. The reason why I was looking for RANK() function was due to a weird behavior of "'Top N Options" in BOC.

Consider I have a simple Table like below.

County - City - Total

Brazil Ghuna 1000

Canada Ottawa 800

India Mumbai 700

India Pune 700

India Delhi 600

If I go by normal RANK function of SQL then output expected would be

County - City - Total RANK

Brazil Ghuna 1000 1

Canada Ottawa 800 2

India Mumbai 700 3

India Pune 700 3

India Delhi 600 5

Now, if I filter by Top 3 (by rank) then I should get first 4 rows and for Bottom 2 I should get bottom 2 rows ( since Mumbai and Pune have a tie for same rank)

When I am trying to replicate the same in BOC I am getting one row lesser in each case. Please see screenshot.

Can you confirm what is the logic of Top N being interpreted by BOC or there sounds a bug in it?

Regards,

Kiran Shenvi

rank-error.png (45.5 kB)
0

Hi Kiran,

I'm not sure. I will follow up to see what is the expected behavior.

Thanks!

0
Kiran Shenvi Jan 23, 2017 at 02:33 PM
0

Hi John,

I was trying to work within our environment where we have a LIVE Data Connection with HANA Database and a model associated with it.

When I was trying to consume this model in my story and create a new calculation

it doesn't show the types -> Aggregation and Difference From in the dropdown.

Can you please confirm if this is a known limitation or some issue?


Regards,

Kiran Shenvi


Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Kiran,

Correct that's a limitation. The option to create a new calculation may not appear if calculations are not possible for the chart type or model.

0