6 Replies Latest reply: Jan 15, 2013 7:28 PM by i_need_a_FIX RSS

    How to use a substitution variable in a conditional statement in formula

    PLIM
      Hi all

      I am working with a project forecasting application and have built a simple "estimate to complete (ETC)" member. Its in the Period dimension and for the majority is working fine. I however need to be able to provide a different member formula if a substitution variable (e.g. &CurrentMonth) is set to December.

      The formula I have written is:

      IF(&CurrentMonth == "Dec")
      do this;
      ELSE
      do that;
      ENDIF;

      It validates fine but I seem to get varied results (for some entities its correct and some it isnt). I have tried changing the condition to IF(@ISMBR(&CurrentMonth) == "Dec" and this works better (majority of entities are now correct) but its still not 100% correct. I assume that perhaps the use of substitution variables in member formula conditions is not correct.

      Does anyone know an alternative solution to set a condition based on a month (which is not the period member selected) in a member formula?
        • 1. Re: How to use a substitution variable in a conditional statement in formula
          i_need_a_FIX
          Hi,

          @ISMBR compares the member being calculated with the one in arguments

          Fix on all periods, then use in the script IF(@ISMBR(&CURRENTMONTH))
          • 2. Re: How to use a substitution variable in a conditional statement in formula
            PLIM
            Thanks for the reply JM.

            Actually as I mentioned the ETC member is in the Period dimension. And its in a member formula (its dynamic calc) and therefore I cant fix any members since its not in a business rule. Basically the formula is the sum of the remaining months of the current year plus the sum of the year total of all future years. However, when we are in the month of December, its only the sum of the year total of all future years.

            This is why I'm using a substitution variable since that is the only way I know how to reference a constant in a member formula.

            Any other ideas?
            • 3. Re: How to use a substitution variable in a conditional statement in formula
              i_need_a_FIX
              Oh I see. Well, string comparison has never been dealt with properly in calc script (although I agree it wouldn't be a huge effort for developers...).

              Anyway, in that case, how about creating a variable like &ITSDECEMBER that you set to 0 or 1 and check
              IF(&ITSDECEMBER == 1)
              ...



              Or if you can't create a new sub variable, be creative! (note that performance may not be as good) How about :

              IF(@COUNT(SKIPNONE,@RANGE("Jan":&CurrentMonth) == 12)
              ...
              ELSE
              ...
              ENDIF

              ?
              • 4. Re: How to use a substitution variable in a conditional statement in for
                i_need_a_FIX
                Oh I see. Well, string comparison has never been dealt with properly in calc script (although I agree it wouldn't be a huge effort for developers...).

                Anyway, in that case, how about creating a variable like &ITSDECEMBER that you set to 0 or 1 and check
                IF(&ITSDECEMBER == 1)
                ...



                Or if you can't create a new sub variable, be creative! (note that performance may not be as good) How about :

                IF(@COUNT(SKIPNONE,@RANGE("Jan":&CurrentMonth) == 12)
                ...
                ELSE
                ...
                ENDIF

                ?
                • 5. Re: How to use a substitution variable in a conditional statement in formula
                  PLIM
                  Thanks again. The introduction of the &itsdecember variable is much the same as just comparing the &currentmonth variable but I did try out the IF(@COUNT(SKIPNONE,@RANGE("Jan":&CurrentMonth) == 12) suggestion. For some reason this gives me the same issue - some member calculations are correct and some dont seem to return a value.

                  HOWEVER!! your suggestions have given me another idea - similar to yours (or perhaps this was what you were thinking). I created a dynamic calc member that just does the same thing (If &CurrentMonth == Dec then 1 else #missing) and that seems to work! No idea why this one does and the other ones dont but I'm not going to argue.

                  Thanks heaps for your help JM!
                  • 6. Re: How to use a substitution variable in a conditional statement in formula
                    i_need_a_FIX
                    PLIM wrote:
                    HOWEVER!! your suggestions have given me another idea - similar to yours (or perhaps this was what you were thinking). I created a dynamic calc member that just does the same thing (If &CurrentMonth == Dec then 1 else #missing) and that seems to work! No idea why this one does and the other ones dont but I'm not going to argue.

                    Thanks heaps for your help JM!
                    This should not work. You cannot perform string comparison using the "==" operator in calcscripts.

                    A last alternative is to use a CDF to perform this comparison.