topic Re: CALCULATE VALUE FROM RANK SELECTION in App Development

CALCULATE VALUE FROM RANK SELECTION

philgood34 — Sat, 21 Oct 2017 07:32:55 GMT

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 ?

thank's in advance

Philippe

its_anandrjs — Sat, 21 Oct 2017 07:59:33 GMT

Try with Rank function for finding top 5 Margin Sales

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

SUM (TOTAL (SALES - COST))

philgood34 — Sat, 21 Oct 2017 08:23:47 GMT

Hi Anand

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

Regards

Philippe

philgood34 — Sat, 21 Oct 2017 08:48:40 GMT

as shown below

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

philgood34 — Sat, 21 Oct 2017 14:08:07 GMT

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

its_anandrjs — Sat, 21 Oct 2017 16:31:14 GMT

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))