2 Replies Latest reply: Oct 24, 2012 5:36 AM by Samuli S. RSS

    How to return highest/lowest value in a group of accounts?

    Samuli S.
      Hi gurus,

      I'm facing a requirement where in HFM, I need to set the value of an account to be the same as the highest value of a group of other accounts (POV is the same, all accounts are lowest level in hierarchy).

      So basically, I need a rule/script/function that enables me to say: "look up the values of account 1, account 2 and account 3 for this point of view, and set the value of account 4 to be whatever is the highest of the values of accounts 1, 2 and 3".

      I would be extremely grateful for any insights and assistance. I'm sure this is possible in VBScript....

        • 1. Re: How to return highest/lowest value in a group of accounts?
          Indeed this is possible in VBScript.

          You don't have handy Max/Min functions in VBscript but given that you've only got three inputs, you can easily compare one number against the other two and deduce the maximum from there.

          First of all, get the data from the 3 intersections you want using GetCell

          Var1 = HS.GetCell("POVExpression")
          Var2 = HS.GetCell("POVExpression")
          Var3 = HS.GetCell("POVExpression")

          Then do a simple comparison of the three values using something like below

          If Var1 >= Var2 And Var1 >= Var3 Then
          VarMax = Var1
          ElseIf Var2 >= Var1 And Var2 >= Var3 Then
          VarMax = Var2
          VarMax = Var3
          End If

          The maximum value will be in the variable VarMax. You can now use HS.Exp to to assign that variable to the desired account