Forum Discussion

tcook's avatar
tcook
New Contributor
16 days 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 a text field of an entity, and create a member formula for a statistical account that calculates the number of months between that acquisition date and whatever month the Point of View is referencing (we could convert the POV month to a specific date if necessary). Is this type of calculation possible within stored member formulas?

Finance
  • sameburn's avatar
    sameburn
    16 days 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
    16 days 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