11 Replies Latest reply: Feb 26, 2013 1:27 PM by MR RSS

    Retrieve current period in member list

    bconley
      Dear all,

      Is there any function in member lists to retrieve the current period? Example:

      Month() = Returns current month

      Many thanks for your help,

      Benoit
        • 1. Re: Retrieve current period in member list
          Anna
          Hi Benoit,

          If you are using MemberList, you need to create a looping. ie:

          AcctList = HS.Account.List("","[Base]")

          For i = Lbound(AcctList) To Ubound(AcctList)

          '--- some parameter

          Next

          Use i to retrieve the n-th member of your member list.

          I hope it helps,
          Anna
          • 2. Re: Retrieve current period in member list
            bconley
            Dear Anna,

            Thanks for your reply.

            My question was unclear though. I meant that I need to have the current date/time of the application. In Excel, function date() or month() retrieves the current time.

            As for an example, Month() would return November as we currently are in November.

            I would like to know if it is possible to get this information in HFM Rules/Member lists?

            Thanks a lot for your help,

            Benoit
            • 3. Re: Retrieve current period in member list
              689381
              Hi Benoit

              I think you need get the actual year or month, here an example how to get them in member lists file definition.

              Sub EnumMemberLists()*

              'as = array of strings
              Dim asYear(1)
              Dim asPeriod(1)

              Select Case HS.Dimension

              Case "Year"
              asYear(1) = "Actual Year"
              HS.SetMemberLists asYear

              Case "Period"
              asPeriod(1) = "Actual Month"
              HS.SetMemberLists asPeriod

              End Select 'HS.Dimension

              End Sub 'EnumMemberLists

              Sub EnumMembersInList()*

              Select Case HS.Dimension

              Case "Year"
              '--------------

              Select Case HS.MemberListID

              Case 1 'Actual Year
              HS.AddMemberToList CStr( Year( Date ) )

              End Select 'HS.MemberListID Year

              Case "Period"
              '--------------

              iMonth = Month( Date )
              'the label for the period is needed
              If iMonth = 1 Then
              sMonth = "JAN"
              '...
              '...
              Else
              sMonth = "DEC"
              End If

              Select Case HS.MemberListID

              Case 1 'Mes Actual
              '-------------------
              HS.AddMemberToList sMonth

              End Select 'HS.MemberListID Period

              End Select 'HS.Dimension

              End Sub 'EnumMembersInList
              • 4. Re: Retrieve current period in member list
                Anna
                Oic, i honestly never use that.

                Have you try Now() function ? It surely can run under Sub WriteToFile. I don't know if you apply it under Sub Calculate.

                -Anna
                • 5. Re: Retrieve current period in member list
                  MR
                  I used a simple model using the Now() function, like another user said. I subtracted 28 from the current date since Feb is the shortest month in the year and this was the easiest way I would be able to derive "Feb" as the current fiscal month in March (when we do Feb close).

                  I plan on expanding this function later to determine the number of days to subtract based on which month the system is currently reading.


                  Sub Current_Fiscal_Period()
                  {
                       Today = Now()
                       Prior_Month_Date = Now() - 28
                       Prior_Mo_Date_Num = Month(PrevMoDate)
                       Previous_Month = Left(MonthName(PrevMonthNum),3)
                       
                       HS.AddMemberToList Previous_Month
                  }     
                  End Sub

                  Edited by: MR on Jan 8, 2013 1:49 PM
                  • 6. Re: Retrieve current period in member list
                    beyerch2
                    I do not recommend using this code.....
                    Prior_Month_Date = Now() - 28
                    IF you want to determine the previous month, use the DateAdd command...
                    Prior_Month_Date = DateAdd("m", -1, Date())
                    Charles
                    • 7. Re: Retrieve current period in member list
                      bconley
                      Thanks for your help.

                      Is dateadd avaiable without calculation manager?

                      Best regards,

                      Benoit
                      • 8. Re: Retrieve current period in member list
                        JTF85
                        I know metadata lists are brought into memory when the system is restarted, so would you therefore need to restart the HFM services for the memberlist to update each month?

                        JTF
                        • 9. Re: Retrieve current period in member list
                          MR
                          For my knowledge, could you please tell me why you would not recommend using the Now()-28?

                          Thank you!

                          MR
                          • 10. Re: Retrieve current period in member list
                            beyerch2
                            You answered the question in your original post:

                            "I plan on expanding this function later to determine the number of days to subtract based on which month the system is currently reading. "

                            Why worry about the number of days to subtract when you can just tell VBA to get the previous month and let it worry about the details? Also -28 doesn't account for leap years apparently?
                            • 11. Re: Retrieve current period in member list
                              MR
                              That makes sense. I was planning on accounting for leap years using the mod math function, but your method is much easier. I changed the code to use the dateadd function and it worked great!

                              Thanks again!