Forum Discussion

tcook's avatar
tcook
New Contributor
2 months ago

Date-Based Math in Member Formulas

We have a requirement to create reporting that utilizes the number of months since a company was acquired to perform math on specific columns of data. We would like to attach the acquisition date in ...
Finance
  • sameburn's avatar
    sameburn
    2 months ago

    Hi tcook

    I would recommend converting your DateTime value into a relatable OneStream Time dimension member e.g. 01/01/2024 will be 2024M1.  You should be able to do this using some string manipulation and the TimeDimHelper class to derive period count between dates.  In example below this would be 8 months

    				' Example Acquisition Date As String
				Dim acqDate As String = "11/19/2023"
				' Example conversion to OneStream Time e.g. 2023M11
				Dim acqTimeSplit As String() = acqDate.Split("/")
				Dim acqTime As String = String.Format("{0}M{1}", acqTimeSplit(2), acqTimeSplit(0))
								
				' Example POV Time e.g. 2024M6
				Dim povTime As String = "2024M6"

				' Get Time Ids using TimeDimHelper class 
				Dim startTime As Integer = TimeDimHelper.GetIdFromName(acqTime)
				Dim endTime As Integer = TimeDimHelper.GetIdFromName(povTime)
				
				' Create placeholder
				Dim numMonths As Integer = 0
				
				' Check variable logic is correct
				If Not startTime > endTime Then
				
					' Derive list of times within range
					Dim timeRange As List(Of Integer) = TimeDimHelper.GetIdsInRange(startTime, endTime)

					'  Assign Value to placeholder
					numMonths = timeRange.Count
				
				End If
				
				' Log Result âž¡ Throw Error
				Throw New XFException(numMonths)

    Hope this helps

    Sam

sameburn's avatar
sameburn
Contributor II
2 months ago

Hi tcook

I would recommend converting your DateTime value into a relatable OneStream Time dimension member e.g. 01/01/2024 will be 2024M1.  You should be able to do this using some string manipulation and the TimeDimHelper class to derive period count between dates.  In example below this would be 8 months

				' Example Acquisition Date As String
				Dim acqDate As String = "11/19/2023"
				' Example conversion to OneStream Time e.g. 2023M11
				Dim acqTimeSplit As String() = acqDate.Split("/")
				Dim acqTime As String = String.Format("{0}M{1}", acqTimeSplit(2), acqTimeSplit(0))
								
				' Example POV Time e.g. 2024M6
				Dim povTime As String = "2024M6"

				' Get Time Ids using TimeDimHelper class 
				Dim startTime As Integer = TimeDimHelper.GetIdFromName(acqTime)
				Dim endTime As Integer = TimeDimHelper.GetIdFromName(povTime)
				
				' Create placeholder
				Dim numMonths As Integer = 0
				
				' Check variable logic is correct
				If Not startTime > endTime Then
				
					' Derive list of times within range
					Dim timeRange As List(Of Integer) = TimeDimHelper.GetIdsInRange(startTime, endTime)

					'  Assign Value to placeholder
					numMonths = timeRange.Count
				
				End If
				
				' Log Result âž¡ Throw Error
				Throw New XFException(numMonths)

Hope this helps

Sam