Forum Discussion

Rams_2022's avatar
Rams_2022
New Contributor II
4 months ago

Business rule didn't give results

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
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

 

Finance
  • sameburn's avatar
    sameburn
    Contributor II
    4 months 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's avatar
      Rams_2022
      New Contributor II
      4 months ago

      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?

  • SimonHesford's avatar
    SimonHesford
    Contributor
    4 months 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's avatar
      Rams_2022
      New Contributor II
      4 months ago

      SimonHesford 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? 

      • SimonHesford's avatar
        SimonHesford
        Contributor
        4 months 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? 

  • rhankey's avatar
    rhankey
    Contributor
    4 months 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.

  • Rams_2022's avatar
    Rams_2022
    New Contributor II
    4 months ago

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

  • TheJonG's avatar
    TheJonG
    Contributor III
    4 months 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's avatar
      Rams_2022
      New Contributor II
      4 months 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's avatar
        Rams_2022
        New Contributor II
        4 months 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's avatar
    Rams_2022
    New Contributor II
    4 months 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?