Skip to main content
S
Sweez
December 2, 2024

Convert Date to Period

  • December 2, 2024
  • 2 replies
  • 0 views

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?

2 replies

M
manthangandhi
December 2, 2024

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

 

S
SweezAuthor
December 2, 2024

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)}")
S
SweezAuthor
December 2, 2024

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.

M
manthangandhi
December 2, 2024

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.

Terms & ConditionsAccessibility statement