4 Replies Latest reply on Sep 11, 2019 1:29 PM by Paul Timofti

# Incorporating a Top N parameter inside a calculation

Hello

I have a calculated field with the following code:

CASE [View Selector]

WHEN "CY" THEN

IF

sum([CF Fixed CY-0 Inc Net Sales])<1000 THEN   sum([CF Fixed CY-0 Inc Net Sales])

else sum([CF Fixed CY-0 Inc Net Sales])/1000

end

WHEN "3YRFW"  THEN

IF

(sum([CF Fixed CY+3 Inc Net Sales]) -

(SUM([CF Fixed CY+2 Inc Net Sales])-SUM([CF Fixed CY+1 Inc Net Sales]))

) <1000 then

(

sum([CF Fixed CY+3 Inc Net Sales]) -

(SUM([CF Fixed CY+2 Inc Net Sales])-SUM([CF Fixed CY+1 Inc Net Sales]))

)

else

( sum([CF Fixed CY+3 Inc Net Sales]) -

(SUM([CF Fixed CY+2 Inc Net Sales])-SUM([CF Fixed CY+1 Inc Net Sales]))

)/1000

END

END

Explanations:

Users have a parameter where they can select to see the values for the current year ("CY") or for 3 years in advance ("3YRFW").

I am asked to add a second parameter with values yes or no, for the following question : "Include only top 5 projects?"

How can i add this extra condition in my calculated field? I cannot figure it out how to add the ranking logic (Descending) to this.

Basically, I want something like

CASE [View Selector]

WHEN "CY" THEN

case when [Include only top 5 projects?]

WHEN "YES" THEN

IF

add ranking logic here

sum([CF Fixed CY-0 Inc Net Sales])<1000 THEN   sum([CF Fixed CY-0 Inc Net Sales])

else sum([CF Fixed CY-0 Inc Net Sales])/1000

end

and so on...

Thank you