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

    If condition based on data in form


      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??




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

          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



          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

            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.




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

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


    '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

                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.




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

                  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?



                  • 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.