6 Replies Latest reply on Jul 21, 2017 2:32 PM by T Gupta

    If condition based on data in form

    HFar

      Hi All,

       

      Having trouble in executing figure based on data on form:

       

       

      This is my data form.

       

      I am trying to calculate Quantity * Unit Cost in 4th Column if 3rd column is 1.

       

       

      Following Calculate script I am trying to execute but no luck so far.

       

      Sub Region_Working_2(bLog)

        '

        If HS.GetCell("A#MGT Taxation.I#[ICP None].C1#200011717065.C2#[None].C3#[None].C4#[None]") = 1 Then

         

          HS.Exp "A#MGT Fixed Overhead = ((A#Quantity) * (A#Unit Cost))"

        End If

       

      End Sub

       

       

      Any Idea what I am missing??

       

       

      Regards,

        • 1. Re: If condition based on data in form
          Jeo123

          That's going to give you a bit of a problem.  As soon as one Custom1 passes your if statement, you're going to be calculating that target account for all custom1s.  Also, just to reframe how you think about rules in HFM, your rule isn't tied to a particular form.  The only thing your rule knows is the Scenario, Year, Entity, and Period.  But when you calculate, it's going to run for everything in that combination(not just the data in your form).

           

          To address the main reason you're probably failing, generally HFM account names don't have spaces(not sure if there's away round that using EPMA, but either way it's not common if even possible).  Your form is probably showing you the descriptions.  When writing rules, you need to use the member name.

           

          What you should be doing is either looping through All Custom 1 members and checking this one by one, then you can define the custom one member in your HS.EXP function.  Something like this would work if you want to run this calculation for all Custom1 members.

           

          C1List = HS.Custom1.List("","[Base]")

          for i = LBound(C1List) to UBound(C1List)

            If HS.GetCell("A#MGT Taxation.I#[ICP None].C1#" & C1List(i) & ".C2#[None].C3#[None].C4#[None]") = 1 Then

                 HS.Exp "A#MGT Taxation.C1#" & C1List(i) & "= ((A#Quantity.C1#" & C1List(i) & ") * (A#Unit Cost.C1#" & C1List(i) & "))"

            End If

          next

           

          Note: I've left your account names alone, but you'll need to make sure they're updated to the member names.

           

          Also, make sure you're careful about the other dimensions.  I've left them off since I'm not sure your dimension requirements, but this logic will Units in every Custom 2, 3, and 4, with Cost in those same Custom 2, 3, and 4, and store them in Taxation by the same custom 2, 3, and 4.  If your dimension requirements aren't the same between all 3 accounts, you'll wind up with an issue.

          1 person found this helpful
          • 2. Re: If condition based on data in form
            HFar

            Hi Jeo,

             

            Thanks. Let me check with the solution provided by you. In the meanwhile, let me tell you that in our instance, we can use space for Account Dimension members.

             

             

            Regards,

            • 3. Re: If condition based on data in form
              Jeo123

              I stand corrected, looks like they removed that restriction as of 11.1.2.3 and I never noticed it.  Old habit of just avoiding them.

               

              11.1.1.3's metadata stated:

              Member Specifies the name for the account. This attribute is required. The name must be unique.

              It can contain up to 80 characters, including spaces, but cannot start with a space.

              Note: Account names cannot contain spaces if you are using an Oracle database.

              Do not use these characters in an account name:

              l Period ( . )

              l Plus sign ( + )

              l Minus sign ( - )

              l Asterisk ( * )

              l Slash mark ( / )

              l Number sign ( # )

              l Comma ( , )

              l Semicolon ( ; )

              l At sign ( @ )

              l Double quote ( “ )

              l Curly brackets ( { } )

               

              I just never bothered to look into whether they could now be used.

              • 4. Re: If condition based on data in form
                HFar

                Tried but Didn't work. HFM rules are very tricky to understand.

                 

                But thanks you gave me idea how does HFM rules works. Will do research work on that.

                 

                 

                Regards,

                • 5. Re: If condition based on data in form
                  HFar

                  Please correct me if there is an understanding gap.

                   

                  Account, ICP and All Customs against Period makes a Sub-Cube. If for same Entity, Account and Customs figure is stores for 2 different periods (say Jan = 100 and Feb = 200) then these 2 figures are stores in different subcubes?

                   

                  Regards,

                  • 6. Re: If condition based on data in form
                    T Gupta

                    I think this will be one sub cube only.

                    A subcube is made up of:

                    • 1 member from Scenario, Year, Entity, and Value dimensions
                    • all members of Account, ICP, View, and any Custom dimensions
                    • Each Account, ICP, View, and Custom dimension combination contains 12 values which is for the 12 months of the Period dimension.