5 Replies Latest reply: Oct 21, 2017 12:31 PM by Anand Chouhan

# CALCULATE VALUE FROM RANK SELECTION

Hi

I want to calculate the quota of the TOP FIVE sectors for the margin indicator in a KPI

Dimension SECTORS

Measures  : SALES

COST

and MARGIN = SALES - COST

so in my KPI i 'm waiting for

SUM( top five (SALES-COST)) / SUM (TOTAL (SALES - COST))

Can you help me to find the right syntax using RANK ?

Philippe

• ###### Re: CALCULATE VALUE FROM RANK SELECTION

Try with Rank function for finding top 5 Margin Sales

=if( Rank(SUM (SALES-COST) ) <= 5, SUM (SALES-COST) )

/

SUM (TOTAL (SALES - COST))

• ###### Re: CALCULATE VALUE FROM RANK SELECTION

Hi Anand

Thank's for your answer, but unfortunetly, the result is 100 % (i tested in 2 of my apps )

Regards

Philippe

• ###### Re: CALCULATE VALUE FROM RANK SELECTION

as shown below

the SUM(TOTAL(SALES-COST)) is the total for the top five and not the total of the all selection ...

• ###### Re: CALCULATE VALUE FROM RANK SELECTION

hI

I finaly found a correct syntax without "IF" (i didn't understood why IF without THEN in the Anand suggestion ...)

so i apply

=Sum({<SECTORS_LIB = {"=Rank(Sum(SALES-COST))<=2"}>} (SALES-COST))/Sum(TOTAL (SALES-COST))

(see the result below with few changes)

regards

Philippe

• ###### Re: CALCULATE VALUE FROM RANK SELECTION

That's right Phillipe but it is working at my end may be some typo or maybe dimension issue there in your chart kidly check this.

This is the expression

=SUM( {<Dim1 = {"Rank(SUM (SALES-COST) ) <= 5"}>} SALES-COST)

/

SUM (TOTAL (SALES - COST))