OneStream Community

Rams_2022 · 2 weeks ago

Dear OS Experts,

really appreciate your support on this, I have written a business rule for some generic calculations based on the calender months. the rule is working for USD currency but it not working for CAD. Any suggestions on this code corrections?

Namespace OneStream.BusinessRule.Finance.Temp234Calc

Public Class MainClass

#Region "Main - xxx Calculation"

Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs) As Object

Try

Dim timePeriod As String = api.Pov.Time.Name

Dim entityCode As String = api.Pov.Entity.Name

Dim monthNumber As SByte = api.Time.GetPeriodNumFromId

Dim yearNumber As Short = TimeDimHelper.GetSubComponentsFromName(timePeriod).Year

Dim currScenario As String = api.Pov.Scenario.Name

Dim currCons As ViewMember = api.Scenario.GetConsolidationView()

Dim localCurrency As String = api.pov.Cons.Name

Dim conversionMonth As String = args.CustomCalculateArgs.NameValuePairs("ConversionMonth")

Dim conversionYear As String = args.CustomCalculateArgs.NameValuePairs("ConversionYear")

Dim parentEntity As String = args.CustomCalculateArgs.NameValuePairs("LegacyParentEntity")

Dim monthShift As Dictionary(Of String, MonthShiftValues) = CreateMonthShiftDict(localCurrency)

Dim iter As MonthShiftValues = Nothing

monthShift.TryGetValue(timePeriod, iter)

Dim priorPeriodRevenue As Decimal = api.Data.GetDataCell("E#" & parentEntity & ":A#NET_SALES:T#PovPrior1:V#Periodic").CellAmount

Dim currentPeriodRevenue As Decimal = api.Data.GetDataCell("E#" & parentEntity & ":A#NET_SALES:T#Pov:V#Periodic").CellAmount

Dim nextPeriodRevenue As Decimal = api.Data.GetDataCell("E#" & parentEntity & ":A#NET_SALES:T#PovNext1:V#Periodic").CellAmount

Dim priorPeriodValid As Boolean = False

Dim currentPeriodValid As Boolean = False

Dim nextPeriodValid As Boolean = False

If priorPeriodRevenue > 0 Then

priorPeriodValid = True

End If

If currentPeriodRevenue > 0 Then

currentPeriodValid = True

End If

If nextPeriodRevenue > 0 Then

nextPeriodValid = True

End If

If (Not api.Entity.HasChildren()) And currScenario = "Actual" Then

Dim priorPeriodAdj As DataBuffer

Dim currentPeriodAdj As DataBuffer

Dim nextPeriodAdj As DataBuffer

If priorPeriodValid And nextPeriodValid Then

priorPeriodAdj = api.Data.GetDataBufferUsingFormula("DIVIDE(FilterMembers(T#PovPrior1:V#Periodic:U1#Fro_Prog_Mgt_Stats_Rules123, [E#["& entityCode &"]], [A#[INCOME_MGMT].Base]) * "& CStr(iter.prevMonth) &", "& CStr(iter.prevMonthDays) &")")

currentPeriodAdj = api.Data.GetDataBufferUsingFormula("DIVIDE(FilterMembers(T#Pov:V#Periodic:U1#Fro_Prog_Mgt_Stats_Rules123, [E#["& entityCode &"]], [A#[INCOME_MGMT].Base]) * "& CStr(iter.currMonth) &", "& CStr(iter.currMonthDays) &")")

nextPeriodAdj = api.Data.GetDataBufferUsingFormula("DIVIDE(FilterMembers(T#PovNext1:V#Periodic:U1#Fro_Prog_Mgt_Stats_Rules123, [E#["& entityCode &"]], [A#[INCOME_MGMT].Base]) * "& CStr(iter.nextMonth) &", "& CStr(iter.nextMonthDays) &")")

ElseIf (Not priorPeriodValid) And nextPeriodValid Then

priorPeriodAdj = api.Data.GetDataBufferUsingFormula("DIVIDE(FilterMembers(T#Pov:V#Periodic:U1#Fro_Prog_Mgt_Stats_Rules123, [E#["& entityCode &"]], [A#[INCOME_MGMT].Base]) * "& CStr(iter.prevMonth) &", "& CStr(iter.currMonthDays) &")")

currentPeriodAdj = api.Data.GetDataBufferUsingFormula("DIVIDE(FilterMembers(T#Pov:V#Periodic:U1#Fro_Prog_Mgt_Stats_Rules123, [E#["& entityCode &"]], [A#[INCOME_MGMT].Base]) * "& CStr(iter.currMonth) &", "& CStr(iter.currMonthDays) &")")

nextPeriodAdj = api.Data.GetDataBufferUsingFormula("DIVIDE(FilterMembers(T#PovNext1:V#Periodic:U1#Fro_Prog_Mgt_Stats_Rules123, [E#["& entityCode &"]], [A#[INCOME_MGMT].Base]) * "& CStr(iter.nextMonth) &", "& CStr(iter.nextMonthDays) &")")

ElseIf priorPeriodValid And (Not nextPeriodValid) Then

priorPeriodAdj = api.Data.GetDataBufferUsingFormula("DIVIDE(FilterMembers(T#PovPrior1:V#Periodic:U1#Fro_Prog_Mgt_Stats_Rules123, [E#["& entityCode &"]], [A#[INCOME_MGMT].Base]) * "& CStr(iter.prevMonth) &", "& CStr(iter.prevMonthDays) &")")

currentPeriodAdj = api.Data.GetDataBufferUsingFormula("DIVIDE(FilterMembers(T#Pov:V#Periodic:U1#Fro_Prog_Mgt_Stats_Rules123, [E#["& entityCode &"]], [A#[INCOME_MGMT].Base]) * "& CStr(iter.currMonth) &", "& CStr(iter.currMonthDays) &")")

nextPeriodAdj = api.Data.GetDataBufferUsingFormula("DIVIDE(FilterMembers(T#Pov:V#Periodic:U1#Fro_Prog_Mgt_Stats_Rules123, [E#["& entityCode &"]], [A#[INCOME_MGMT].Base]) * "& CStr(iter.nextMonth) &", "& CStr(iter.currMonthDays) &")")

ElseIf (Not priorPeriodValid) And (Not nextPeriodValid) Then

priorPeriodAdj = api.Data.GetDataBufferUsingFormula("DIVIDE(FilterMembers(T#Pov:V#Periodic:U1#Fro_Prog_Mgt_Stats_Rules123, [E#["& entityCode &"]], [A#[INCOME_MGMT].Base]) * "& CStr(iter.prevMonth) &", "& CStr(iter.currMonthDays) &")")

currentPeriodAdj = api.Data.GetDataBufferUsingFormula("DIVIDE(FilterMembers(T#Pov:V#Periodic:U1#Fro_Prog_Mgt_Stats_Rules123, [E#["& entityCode &"]], [A#[INCOME_MGMT].Base]) * "& CStr(iter.currMonth) &", "& CStr(iter.currMonthDays) &")")

nextPeriodAdj = api.Data.GetDataBufferUsingFormula("DIVIDE(FilterMembers(T#Pov:V#Periodic:U1#Fro_Prog_Mgt_Stats_Rules123, [E#["& entityCode &"]], [A#[INCOME_MGMT].Base]) * "& CStr(iter.nextMonth) &", "& CStr(iter.currMonthDays) &")")

End If

api.Data.FormulaVariables.SetDataBufferVariable("priorPeriodAdj", priorPeriodAdj, True)

api.Data.FormulaVariables.SetDataBufferVariable("currentPeriodAdj", currentPeriodAdj, True)

api.Data.FormulaVariables.SetDataBufferVariable("nextPeriodAdj", nextPeriodAdj, True)

Select Case yearNumber

Case = conversionYear

If monthNumber = 1 And conversionMonth = 1 Then

api.Data.Calculate(

"E#" & entityCode & ":V#Periodic:O#Import:U1#123_Adjustment = 0"

)

ElseIf monthNumber > 1 And conversionMonth = 1 Then

api.Data.Calculate(

"E#" & entityCode & ":V#YTD:U1#123_Adjustment = FilterMembers(T#PovPrior1:V#YTD:U1#123_Adjustment, [A#[INCOME_MGMT].Base])"

)

ElseIf monthNumber = 1 And conversionMonth <> 1 And currentPeriodValid Then

api.Data.Calculate("E#" & entityCode & ":V#Periodic:U1#123_Adjustment = $priorPeriodAdj + $currentPeriodAdj + $nextPeriodAdj")

ElseIf monthNumber > 1 And monthNumber < conversionMonth And conversionMonth <> 1 And currentPeriodValid Then

api.Data.Calculate("E#" & entityCode & ":V#YTD:U1#123_Adjustment = FilterMembers(T#PovPrior1:V#YTD:U1#123_Adjustment, [A#[INCOME_MGMT].Base]) + $priorPeriodAdj + $currentPeriodAdj + $nextPeriodAdj")

ElseIf monthNumber >= conversionMonth And conversionMonth <> 1 Then

api.Data.Calculate("E#" & entityCode & ":V#YTD:U1#123_Adjustment = FilterMembers(T#PovPrior1:V#YTD:U1#123_Adjustment, [A#[INCOME_MGMT].Base])")

End If

Case < conversionYear

If monthNumber = 1 And currentPeriodValid Then

api.Data.Calculate("E#" & entityCode & ":V#Periodic:U1#123_Adjustment = $priorPeriodAdj + $currentPeriodAdj + $nextPeriodAdj")

ElseIf monthNumber > 1 And currentPeriodValid Then

api.Data.Calculate("E#" & entityCode & ":V#YTD:U1#123_Adjustment = FilterMembers(T#PovPrior1:V#YTD:U1#123_Adjustment, [A#[INCOME_MGMT].Base]) + $priorPeriodAdj + $currentPeriodAdj + $nextPeriodAdj")

End If

End Select

ElseIf (Not api.Entity.HasChildren()) And currScenario <> "Actual" And currCons.Name = "Periodic" Then

If monthNumber >= 1 Then

api.Data.Calculate(

"E#" & entityCode & ":V#Periodic:U1#123_Adjustment =

DIVIDE(FilterMembers(T#PovPrior1:V#Periodic:U1#Fro_Prog_Mgt_Stats_Rules123, [E#["& entityCode &"]], [A#[INCOME_MGMT].Base]) * "& CStr(iter.prevMonth) &", "& CStr(iter.prevMonthDays) &") +

DIVIDE(FilterMembers(T#Pov:V#Periodic:U1#Fro_Prog_Mgt_Stats_Rules123, [E#["& entityCode &"]], [A#[INCOME_MGMT].Base]) * "& CStr(iter.currMonth) &", "& CStr(iter.currMonthDays) &") +

DIVIDE(FilterMembers(T#PovNext1:V#Periodic:U1#Fro_Prog_Mgt_Stats_Rules123, [E#["& entityCode &"]], [A#[INCOME_MGMT].Base]) * "& CStr(iter.nextMonth) &", "& CStr(iter.nextMonthDays) &")

"

)

End If

ElseIf (Not api.Entity.HasChildren()) And currScenario <> "Actual" And currCons.Name = "YTD" Then

If monthNumber = 1 Then

api.Data.Calculate(

"E#" & entityCode & ":V#Periodic:U1#123_Adjustment =

DIVIDE(FilterMembers(T#PovPrior1:V#Periodic:U1#Fro_Prog_Mgt_Stats_Rules123, [E#["& entityCode &"]], [A#[INCOME_MGMT].Base]) * "& CStr(iter.prevMonth) &", "& CStr(iter.prevMonthDays) &") +

DIVIDE(FilterMembers(T#Pov:V#Periodic:U1#Fro_Prog_Mgt_Stats_Rules123, [E#["& entityCode &"]], [A#[INCOME_MGMT].Base]) * "& CStr(iter.currMonth) &", "& CStr(iter.currMonthDays) &") +

DIVIDE(FilterMembers(T#PovNext1:V#Periodic:U1#Fro_Prog_Mgt_Stats_Rules123, [E#["& entityCode &"]], [A#[INCOME_MGMT].Base]) * "& CStr(iter.nextMonth) &", "& CStr(iter.nextMonthDays) &")

"

)

ElseIf monthNumber > 1 Then

api.Data.Calculate(

"E#" & entityCode & ":V#YTD:U1#123_Adjustment = FilterMembers(T#PovPrior1:V#YTD:U1#123_Adjustment, [A#[INCOME_MGMT].Base]) +

DIVIDE(FilterMembers(T#PovPrior1:V#Periodic:U1#Fro_Prog_Mgt_Stats_Rules123, [E#["& entityCode &"]], [A#[INCOME_MGMT].Base]) * "& CStr(iter.prevMonth) &", "& CStr(iter.prevMonthDays) &") +

DIVIDE(FilterMembers(T#Pov:V#Periodic:U1#Fro_Prog_Mgt_Stats_Rules123, [E#["& entityCode &"]], [A#[INCOME_MGMT].Base]) * "& CStr(iter.currMonth) &", "& CStr(iter.currMonthDays) &") +

DIVIDE(FilterMembers(T#PovNext1:V#Periodic:U1#Fro_Prog_Mgt_Stats_Rules123, [E#["& entityCode &"]], [A#[INCOME_MGMT].Base]) * "& CStr(iter.nextMonth) &", "& CStr(iter.nextMonthDays) &")

"

)

End If

Return Nothing

Catch ex As Exception

Throw ErrorHandler.LogWrite(si, New XFException(si, ex))

End Try

End Function

#End Region

Public Function CreateMonthShiftDict(ByVal Currency As String) As Dictionary(Of String, MonthShiftValues)

If Currency = "USD" Then

Dim monthShift_USD As New Dictionary(Of String, MonthShiftValues)

monthShift_USD.Add("2023M1",New MonthShiftValues(2,-2,0,21,20,23))

monthShift_USD.Add("2023M2",New MonthShiftValues(2,-5,0,20,23,20))

monthShift_USD.Add("2023M3",New MonthShiftValues(5,0,0,23,20,22))

monthShift_USD.Add("2023M4",New MonthShiftValues(0,-2,0,20,22,22))

monthShift_USD.Add("2023M5",New MonthShiftValues(2,-5,0,22,22,20))

monthShift_USD.Add("2023M6",New MonthShiftValues(5,-1,0,22,20,23))

monthShift_USD.Add("2023M7",New MonthShiftValues(1,-4,0,20,23,20))

monthShift_USD.Add("2023M8",New MonthShiftValues(4,-5,0,23,20,22))

monthShift_USD.Add("2023M9",New MonthShiftValues(5,-2,0,20,22,20))

monthShift_USD.Add("2023M10",New MonthShiftValues(2,-4,0,22,20,20))

monthShift_USD.Add("2023M11",New MonthShiftValues(4,-4,0,20,20,22))

monthShift_USD.Add("2023M12",New MonthShiftValues(4,-3,0,20,22,21))

monthShift_USD.Add("2024M1",New MonthShiftValues(3,-4,0,22,21,21))

monthShift_USD.Add("2024M2",New MonthShiftValues(4,-5,0,21,21,22))

monthShift_USD.Add("2024M3",New MonthShiftValues(5,-2,0,21,22,22))

monthShift_USD.Add("2024M4",New MonthShiftValues(2,-4,0,22,22,20))

monthShift_USD.Add("2024M5",New MonthShiftValues(4,-5,0,22,20,22))

monthShift_USD.Add("2024M6",New MonthShiftValues(5,-3,0,20,22,22))

monthShift_USD.Add("2024M7",New MonthShiftValues(3,-5,0,22,22,20))

monthShift_USD.Add("2024M8",New MonthShiftValues(5,-6,0,22,20,23))

monthShift_USD.Add("2024M9",New MonthShiftValues(6,-4,0,20,23,19))

monthShift_USD.Add("2024M10",New MonthShiftValues(4,-3,0,23,19,21))

monthShift_USD.Add("2024M11",New MonthShiftValues(3,-6,0,19,21,22))

monthShift_USD.Add("2024M12",New MonthShiftValues(6,0,0,21,22,20))

Return monthShift_USD

ElseIf Currency = "CAD" Then

Dim monthShift_CAD As New Dictionary(Of String, MonthShiftValues)

monthShift_CAD.Add("2023M1",New MonthShiftValues(2,-2,0,21,20,23))

monthShift_CAD.Add("2023M2",New MonthShiftValues(2,-5,0,20,23,20))

monthShift_CAD.Add("2023M3",New MonthShiftValues(5,0,0,23,20,22))

monthShift_CAD.Add("2023M4",New MonthShiftValues(0,-2,0,20,22,22))

monthShift_CAD.Add("2023M5",New MonthShiftValues(2,-5,0,22,22,20))

monthShift_CAD.Add("2023M6",New MonthShiftValues(5,-1,0,22,20,23))

monthShift_CAD.Add("2023M7",New MonthShiftValues(1,-4,0,20,23,20))

monthShift_CAD.Add("2023M8",New MonthShiftValues(4,-5,0,23,20,22))

monthShift_CAD.Add("2023M9",New MonthShiftValues(5,-2,0,20,22,20))

monthShift_CAD.Add("2023M10",New MonthShiftValues(2,-4,0,22,20,20))

monthShift_CAD.Add("2023M11",New MonthShiftValues(4,-4,0,20,20,22))

monthShift_CAD.Add("2023M12",New MonthShiftValues(4,-3,0,20,22,21))

monthShift_CAD.Add("2024M1",New MonthShiftValues(3,-4,0,22,21,21))

monthShift_CAD.Add("2024M2",New MonthShiftValues(4,-5,0,21,21,22))

monthShift_CAD.Add("2024M3",New MonthShiftValues(5,-2,0,21,22,22))

monthShift_CAD.Add("2024M4",New MonthShiftValues(2,-4,0,22,22,20))

monthShift_CAD.Add("2024M5",New MonthShiftValues(4,-5,0,22,20,22))

monthShift_CAD.Add("2024M6",New MonthShiftValues(5,-3,0,20,22,22))

monthShift_CAD.Add("2024M7",New MonthShiftValues(3,-5,0,22,22,20))

monthShift_CAD.Add("2024M8",New MonthShiftValues(5,-6,0,22,20,23))

monthShift_CAD.Add("2024M9",New MonthShiftValues(6,-4,0,20,23,19))

monthShift_CAD.Add("2024M10",New MonthShiftValues(4,-3,0,23,19,21))

monthShift_CAD.Add("2024M11",New MonthShiftValues(3,-6,0,19,21,22))

monthShift_CAD.Add("2024M12",New MonthShiftValues(6,0,0,21,22,20))

Return monthShift_CAD

End If

End Function

End Class

#Region "MonthShiftClass"

Public Class MonthShiftValues

Public Property prevMonth As SByte

Public Property currMonth As SByte

Public Property nextMonth As SByte

Public Property prevMonthDays As SByte

Public Property currMonthDays As SByte

Public Property nextMonthDays As SByte

Public Sub New(ByVal prevMonth As SByte, ByVal currMonth As SByte, ByVal nextMonth As SByte, ByVal prevMonthDays As SByte, ByVal currMonthDays As SByte, ByVal nextMonthDays As SByte)

Me.prevMonth = prevMonth

Me.currMonth = currMonth

Me.nextMonth = nextMonth

Me.prevMonthDays = prevMonthDays

Me.currMonthDays = currMonthDays

Me.nextMonthDays = nextMonthDays

End Sub

End Class

#End Region

End Namespace

SH_INT · 2 weeks ago

So much code, so little context 🙂 How are you calling this business rule? Are you sure that "CAD" as a currency is actually set as the localCurrency variable value?

Rams_2022 · 2 weeks ago

@SH_INT I am calling through the DM, I am running with USD, CAD in the "Consolidation filter" and also local but only results are coming for USD only. Local currency is set to USD, not CAD. how to change code to make it work for CAD currency, any suggestions pls?

SH_INT · 2 weeks ago

I have a feeling that the Consolidation filter only lets you specify a single member. Have you tried it with just CAD as the filter criteria?

Rams_2022 · 2 weeks ago

@SH_INT I tried to but no luck. I am thinking something that I am missing in the code. any other thoughts please?

Rams_2022 · 2 weeks ago

@ OS Experts, kindly suggest if anything that I missed can solve this problem?

rhankey · 2 weeks ago

Perhaps it is time to add some temporary debugging statements in your code so you can figure out where it is going off the rails. You are providing way too much code and way too little information for someone else to reverse engineer what might or might not be going on.

The DM Currency Filter most certainly does permit one to provide a delimited list of defined currency members.

TheJonG · 2 weeks ago

As the others have said, you cannot expect to paste in a wall of code with minimal context and expect anyone to be able to help. You have to take several more steps on your own to narrow in on the root cause before posting it here. Here are two functions that will help you:

api.LogMessage("Message")

DataBufferVarName.LogDataBuffer

Rams_2022 · 2 weeks ago

ok thank you @TheJonG @rhankey Just provide more context is that, I used as the Local as currency in "consolidation filter" and ran the DM job. it gives me only the USD entity values in 123_adjustments combinations for FY2023,FY2024 but not CAD entities. Also, I kept the "USD, CAD" currencies in consolidation filter but it gives me the values only the USD entities. my main calculations are more of checks the certain conditions and the months/year specifics. I should expect to see the USD entities and CAD entities data for 123_Adjustments. my USD and CAD entities are siblings and rollup to corresponding parent.

Rams_2022 · 2 weeks ago

I figured out the issue. I missed to update the "Auto Translation Currencies" to USD for the Canada entities as the canada entities currency set to "CAD". now the data is showing after running the DM job. but another issue, the data is getting wiped out after ForceConsolidation DM job running. Anything that I am missing here? kindly share your thoughts?

Rams_2022 · 2 weeks ago

I figured out the issue. I missed to update the "Auto Translation Currencies" to USD for the Canada entities as the Canada entities currency set to "CAD". now the data is showing after running the DM job. but another issue, the data is getting wiped out after ForceConsolidation DM job running. Anything that I am missing here? kindly share your thoughts?

sameburn · a week ago

Hi @Rams_2022 you need to set the isdurabledata boolean to True for each of your api.data.calculate calls in your code e.g

api.Data.Calculate("E#" & entityCode & ":V#YTD:U1#123_Adjustment = FilterMembers(T#PovPrior1:V#YTD:U1#123_Adjustment, [A#[INCOME_MGMT].Base])", True)

Rams_2022 · Wednesday

Thank you @sameburn . Once I added the isdurabledata boolean to True for each of api.data.calculate then I can see that the data is not wiped-out after consolidation. but I cann't see the data rolling up to Country level wherein I have data in baselevel element. Any suggestions?

OneStream Community

Business rule didn't give results