Forum Discussion

Sweez's avatar
Sweez
New Contributor III
20 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
manthangandhi's avatar
manthangandhi
New Contributor III
20 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
    20 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)}")