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

    Incorporating a Top N parameter inside a calculation

    Paul Timofti

      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