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

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

• ###### 1. Re: Incorporating a Top N parameter inside a calculation

One way to approach this would be to add a set for the Top 5 projects:

Then have a parameter for [Include Top Projects]

If [Include Top Projects], then include the set in the criteria. Otherwise, leave it out (and show everything regardless of set).

• ###### 2. Re: Incorporating a Top N parameter inside a calculation

I have the parameter:

I have a calculated field based on this parameter to generate 1 or 0 as I cannot use parameters in TOP conditions, only fields

I've created the set as you specified and added the TOP 5 field and the condition based on the calculated field above:

Now if I use the set as a filter it only displays a value when the parameter is "Yes". For "No" it displays blank.

Can you notice where my mistake is?

• ###### 3. Re: Incorporating a Top N parameter inside a calculation

I think you need an additional filter:

CASE [Top Projects Only]

WHEN "Yes"     THEN [Set Top 5]

ELSE TRUE

END

Filter on true, rather than using the Set as a filter directly.