3 Replies Latest reply: Mar 25, 2018 12:56 PM by Sasidhar Parupudi RSS

    Dynamic Expression

    Dan Coen

      I am trying to write and expression in a straight table that will alternate between Year, Month, Quarter per selectable filters as well as differentiate between "Booked" and "Shipped" orders based on a toggle.

       

      The below expressions is working and updating the measure dynamically but it is does not include Booked Month orders.

       

      =if(GetSelectedCount([Fiscal - Year])>0,

      if($(Surgical_Amount_Type)='Booked Amount',

      if(GetSelectedCount([Fiscal - Year])>0,

      $(aYearBookedToDate))

      ,$(aYearShippedToDate))

      ,

      if(GetSelectedCount([Fiscal - Year Quarter Name Long])>0,

      if($(Surgical_Amount_Type)='Booked Amount',

      if(GetSelectedCount([Fiscal - Year Quarter Name Long])>0,

      $(aQuarterBookedToDate))

      ,$(aQuarterShippedToDate))

      ,

      if(

      GetSelectedCount([Fiscal - Period Name Year Short])>0,

      $(aMonthShippedToDate))))

       

      When I attempt to update the expression to include the Booked Orders variable, the whole expression breaks. Im not sure what I am doing wrong as I have moved the open parenthesis countless times. Below is the piece I added in red. 

       

      =if(GetSelectedCount([Fiscal - Year])>0,

      if($(Surgical_Amount_Type)='Booked Amount',

      if(GetSelectedCount([Fiscal - Year])>0,

      $(aYearBookedToDate))

      ,$(aYearShippedToDate))

      ,

      if(GetSelectedCount([Fiscal - Year Quarter Name Long])>0,

      if($(Surgical_Amount_Type)='Booked Amount',

      if(GetSelectedCount([Fiscal - Year Quarter Name Long])>0,

      $(aQuarterBookedToDate))

      ,$(aQuarterShippedToDate)))

      ,

      if(GetSelectedCount([Fiscal - Period Name Year Short])>0,

      if($(Surgical_Amount_Type)='Booked Amount',

      if(GetSelectedCount([Fiscal - Period Name Year Short])>0,

      $(aMonthBookedToDate))

      ,$(aMonthShippedToDate))))

       

      Any ideas?

        • Re: Dynamic Expression
          Digvijay Singh

          Not sure but may be this -

           

          =if(GetSelectedCount([Fiscal - Year])>0,

          if($(Surgical_Amount_Type)='Booked Amount',$(aYearBookedToDate),$(aYearShippedToDate))

          ,

          if(GetSelectedCount([Fiscal - Year Quarter Name Long])>0,

          if($(Surgical_Amount_Type)='Booked Amount',if(GetSelectedCount([Fiscal - Year Quarter Name Long])>0,$(aQuarterBookedToDate),$(aQuarterShippedToDate)))

          ,

          if(GetSelectedCount([Fiscal - Period Name Year Short])>0,

          if($(Surgical_Amount_Type)='Booked Amount',if(GetSelectedCount([Fiscal - Period Name Year Short])>0,$(aMonthBookedToDate),$(aMonthShippedToDate))))))

          • Re: Dynamic Expression
            Steve Dark

            The code in red has an unbalanced number of parentheses, there are five opening parentheses and only four closing.  You may need another closing one at the end of that section?

             

            Also, what is the content of the Surgical_Amount_Type variable?  If it is a fixed value (rather than code) then you may need to include that in single quote marks, i.e.:

             

            if('$(Surgical_Amount_Type)'='Booked Amount',

             

            Hope that points you in the right direction,

            Steve

            • Re: Dynamic Expression
              Sasidhar Parupudi

              May be try

               

              if(GetSelectedCount([Fiscal - Period Name Year Short])>0,

              if($(Surgical_Amount_Type)='Booked Amount',

              if(GetSelectedCount([Fiscal - Period Name Year Short])>0,

              $(aMonthBookedToDate)))

              ,$(aMonthShippedToDate))))