Hello, I would like to display a ranking of a cost center by spend. Like: top 3% contributer Where I can show the 3% as a KPI. I thought I could use the RANK(spend) WITHIN (ALL Other) function but it keeps displaying 1.00 as the result. Can you help?

Hello Hans, In this case, you would want to use both the RANK and the TOP(n%) functions. You would use the RANK function to get the ranking, which will include all values, and then use TOP to only show the top n (or n% of) results. Here is a simple example metric, using the spend fact you mentioned, for illustration:

SELECT RANK (SUM (Spend)) WHERE TOP (3%) IN (SELECT SUM (SPEND))

As for all the values displaying 1.00, this is most likely being caused by your use of the WITHIN clause. What is the goal of the

WITHIN (ALL Other)

clause? Have you tried not using it altogether?

Alright, I read, I tried and I failed. Imagine your example:

SELECT RANK (SUM (Spend)) WHERE TOP (3%) IN (SELECT SUM (SPEND))

What I would like to display is 3%. Doing so, make the user understand that this cost center is in the top 3% tier of the spend. 1) Can that be done? and 2) Also show a mere ranking number like 4. This cost center is the fourth most spending of them all. I tried select RANK (SUM (Spend)) but got nonsensical answers...

Thank you for explaining further! I see what you mean now. In GoodData, KPIs can only be used to display numerical values. For example, you could use it to display the MAX(Spend) in the dataset, but it would not display which data center ranks there. (that is, it will not slice the value with any attributes). Due to the same reason, the RANK function will not return a useful value on KPI. You can definitely display the information you want, but it would require a different type of visualization. In my screenshot below, I created a Dashboard with a Ranking in a Table, as an example. The table uses a metric that displays only the GDPs in the Top 5% (using a metric similar to the one I mentioned on my first reply), as well as a RANK metric using the Top 5% metric (see screenshot #2), sliced by the Countries. The result is a list of all countries in the Top 5%, the GDP value and where that GDP Ranks.

Thanks Francisco, I will try that out.

Hey @Hans Cornette, if I understand correctly what you want to achieve is: • a rank of a particular Cost Center by its spend • a percentage to which top X percent this cost center belongs to based on their spend And you want both these values as a KPI (that is as a single number, without the need to break it by the actual cost center). And I assume you will be filtering the specific cost center with a dashboard filter or perhaps by a drill down? Are these correct assumptions? If yes, the these metrics should work for you: The rank

SELECT MIN(
  SELECT RANK(spend sum) DESC 
  BY CostCenterId ALL OTHER 
  WITH PF EXCEPT CostCenterId
)

here the

spend sum

is a simple metric

SELECT SUM(spend)

, the

BY CostCenterId ALL OTHER

part makes sure the rank is calculated internally just by the cost center even if it is not broken by it in the insight (i.e. it is a KPI) and the

WITH PF EXCEPT CostCenterID

part ensures that for this calculation the filter for cost center is ignored and therefore the ranking is calculated from all cost centers even if only one is selected the outer

MIN

function just makes sure it can return a single number even if there would be multiple cost centers selected and ensures the inner calculation logic remains unchanged The Percentage To get the percentage represented by the particular cost center, the TOP filter will not help you. You will need to use the CUME_DIST or PERCENT_RANK functions. And the metric logic will be the very same:

SELECT MIN(
  SELECT CUME_DIST(spend sum) DESC
  BY Cost Center Id ALL OTHER
  WITH PF EXCEPT Cost Center Id
)

Please note that if you select more cost centers, these metrics will give you minimum of their rank and percentile (because of that MIN part). I hope this helps.