Forum Discussion

Sweez's avatar
Sweez
New Contributor III
3 days ago

Convert Date to Period

Has anyone had success taking a date, say 12/2/2024, and the cube name I assume, and converting it to a period like 2024M12?

Finance
  • Sweez's avatar
    Sweez
    New Contributor III
    3 days ago

    This was my my frist thought but then quickly considered the case of non-calander year end time profiles.  In such cases this would not work I think.

    • manthangandhi's avatar
      manthangandhi
      New Contributor III
      3 days ago

      I think you can add some additional conditions to handle that. Alternatively, if you can share an example here, I can take a look and help.

  • manthangandhi's avatar
    manthangandhi
    New Contributor III
    3 days ago

    Hi Sweez,

    Do you mean you want to convert the date "12/2/2024" (December 2nd, 2024) to "2024M12" in CubeView? If that's the case, you can use XFBR String functions. Within that, you can implement the logic to split the date into month and year using VB.Net's built-in DateTime functions. Based on this, you can then add "M" between the year and the month.

    Example: You have a Dashboard XFBR String rule named "PeriodHelper" and there is a function within "ConvertDateToPeriod"
     
    Below is the code within the function.

     

    If args.FunctionName.XFEqualsIgnoreCase("ConvertDateToPeriod") Then
	' Input date string
	Dim inputDate As String = args.NameValuePairs.XFGetValue("inputDate")

	' Parse the date string to a DateTime object
	Dim dateValue As DateTime = DateTime.ParseExact(inputDate, "M/d/yyyy", CultureInfo.InvariantCulture)
			
	' Format the date to "yyyy'M'MM"
	Dim outputDate As String = dateValue.ToString("yyyy'M'MM")

	Return outputDate
End If

     

    Further, you can use this code n the cube view as follows:

    BRString(PeriodHelper, ConvertDateToPeriod, inputDate = |!dateParameter!|)

    Hope this helps.

    Thanks,

    Manthan

     

    • Sweez's avatar
      Sweez
      New Contributor III
      3 days ago

      I appreciate the responses so far.  I wanted to post what I can up with that I think gives the flexibility to accomodate any type of calendar.  Below is a class that I wrapped the logic up into:

      Namespace OneStream.BusinessRule.Extender.DatePeriodConverter
      Public Class PeriodDateInfo
      Public Property StartDate As Date
      Public Property EndDate As Date
      Public Property PeriodId As Integer
      Public Property PeriodName As String

      Public Sub New(si As SessionInfo, sDate As Date, eDate As Date, timeId As Integer)
      StartDate = sDate
      EndDate = eDate
      PeriodId = timeId
      PeriodName = BRApi.Finance.Members.GetMemberName(si, DimTypeId.Time, timeId)
      End Sub
      End Class

      Public Class DatePeriodConversion
      Public Property SI As SessionInfo
      Public Property CubeId As Integer
      Public Property ConvertDate As Date
      Public Property PeriodDateInfoList As New List(Of PeriodDateInfo)

      Public Sub New(sInfo As SessionInfo, cbId As Integer, convDate As Date)
      SI = sInfo
      CubeId = cbId
      ConvertDate = convDate
      PopulatePeriodDateInfoList()
      End Sub

      Public Sub PopulatePeriodDateInfoList()
      Dim objDimPk As DimPk = BRApi.Finance.Dim.GetDimPk(SI, "Time")
      Dim memberList As List(Of MemberInfo) = BRApi.Finance.Members.GetMembersUsingFilter(SI, objDimPk, $"T#Root.Base", True)

      For Each memberItemInfo As MemberInfo In memberList
      Dim startDateTime As Date = Date.MinValue
      Dim endDateTime As Date = Date.MinValue

      BRApi.Finance.Time.GetDateRangeForTimePeriod(SI, CubeId, memberItemInfo.Member.MemberId, startDateTime, endDateTime)

      PeriodDateInfoList.Add(New PeriodDateInfo(SI, startDateTime, endDateTime, memberItemInfo.Member.MemberId))
      Next
      End Sub

      Public Function GetPeriodFromDate(targetDate As Date) As String
      For Each periodInfo As PeriodDateInfo In PeriodDateInfoList
      If targetDate >= periodInfo.StartDate AndAlso targetDate <= periodInfo.EndDate Then
      Return periodInfo.PeriodName
      End If
      Next

      Return String.Empty
      End Function
      End Class
      End Namespace

       

       

      Below is an example of how to use the class: 

      Dim cubeId As Integer = BRApi.Finance.Cubes.GetCubeInfo(si, "Houston").Cube.CubeId
      Dim nowTime As Date = Date.Now()
       
      Dim dpConverter As New DatePeriodConversion(si, cubeId, nowTime)
       
      BRApi.ErrorLog.LogMessage(si, $"Converted Period Name={dpConverter.GetPeriodFromDate(nowTime)}")