Forum Discussion

OSAdmin's avatar
OSAdmin
Valued Contributor
5 years ago

Does anyone have an example of how to apply the Confirmation Rule across several years, displaying each value separately?

Originally posted by Rassie Smit

12/13/2019

Does anyone have an example of how to apply the Confirmation Rule across several years, displaying each value separately?

  • OSAdmin's avatar
    OSAdmin
    Valued Contributor
    Originally posted by Sam Richards

    Here is a rule I used to look at a model result vs another result to make sure they are equal and display any month that doesn't match... you have to tweak the logic a little bit because the display value is tricky when running across years. I use the value to show how many errors there are and then the info fields to show what it is. Let me know if you need some help figuring out what it is doing. 'This is to check the balance of New Accounts to make sure it isn't zero for consective months

     

    Dim i As Long = 0
    Dim strErrorAcct As String
    Dim ScenarioName As String = api.Pov.Scenario.Name
    Dim ScenarioYear As String = Mid(ScenarioName,Instr(ScenarioName,""M"")-4,4)
    Dim ScnYear As Integer = ScenarioYear.XFConvertToInt
    Dim ScenarioMonth As String = Mid(ScenarioName,Instr(ScenarioName,""M"")+1,2)
    Dim ScnMth As Integer = ScenarioMonth.XFConvertToInt

     

    If ScnMth >=3 Then
    'Run for current year and next year and 2 years out if the month is greater then Febuary
    Dim MonthsToProcess As List(Of MemberInfo) = api.Members.GetMembersUsingFilter(api.Pov.TimeDim.DimPk,""T#[""& ScenarioYear &""M""& ScenarioMonth &""].AllNextInYearInclusive,T#[YearNext1(""& ScenarioYear &"")].base,T#[YearNext2(""& ScenarioYear &"")].base"",Nothing)
    For Each Month As MemberInfo In MonthsToProcess
    'Pull in the total Model and the Total QRM Results
    Dim QRMTotal As Decimal = api.Data.GetDataCell(""E#UK_FTPDepts:C#GBP:T#""& Month.Member.Name &"":V#Periodic:A#AC0005456:F#EndBal_Load:O#Top:I#Top:U1#UK_Top:U2#UK_Top:U3#UK_Top:U4#UK_Top:U5#UK_Top:U6#UK_Top:U7#UK_Top:U8#UK_Top"").CellAmount
    Dim ModelTotal As Decimal = api.Data.GetDataCell(""E#UK_OR73921:C#GBP:T#""& Month.Member.Name &"":V#Periodic:A#AC0005456:F#EndBal_Load:O#Top:I#Top:U1#UK_Top:U2#UK_Top:U3#UK_Top:U4#UK_Top:U5#UK_Top:U6#UK_Top:U7#UK_Top:U8#UK_Top"").CellAmount
    'Calculated the difference betwee QRM and Model
    Dim Variance As Decimal = QRMTotal - ModelTotal
    'Brapi.ErrorLog.LogMessage(si, Variance.ToString)
    'If variance isn't equal to 0 then log it as a month to display in the rule
    If Variance <> 0 Then
    If i = 0 Then
    strErrorAcct = (""In ""& Month.Member.Name & "" the Model Total and QRM Total Do Not Tie."")
    Else
    strErrorAcct = strErrorAcct & vbCrLf & (""In ""& Month.Member.Name & "" the Model Total and QRM Total Do Not Tie."")
    End If
    i = (i+1)
    End If

    Next
    Else
    'Run for current year and next year if in January or Febuary
    Dim MonthsToProcess As List(Of MemberInfo) = api.Members.GetMembersUsingFilter(api.Pov.TimeDim.DimPk,""T#[""& ScenarioYear &""M""& ScenarioMonth &""].AllNextInYearInclusive,T#[YearNext1(""& ScenarioYear &"")].base"",Nothing)
    For Each Month As MemberInfo In MonthsToProcess
    'Pull in the total Model and the Total QRM Results
    Dim QRMTotal As Decimal = api.Data.GetDataCell(""E#UK_FTPDepts:T#""& Month.Member.Name &"":C#Local:V#Periodic:A#AC0005456:F#EndBal:O#Top:I#None:U1#CAN_Top:U2#CAN_Top:U3#CAN_Top:U4#CAN_Top:U5#CAN_Top:U6#CAN_Top:U7#CAN_Top:U8#CAN_Top"").CellAmount
    Dim ModelTotal As Decimal = api.Data.GetDataCell(""E#UK_OR73921:T#""& Month.Member.Name &"":C#Local:V#Periodic:A#AC0005456:F#EndBal:O#Top:I#None:U1#CAN_Top:U2#CAN_Top:U3#CAN_Top:U4#CAN_Top:U5#CAN_Top:U6#CAN_Top:U7#CAN_Top:U8#CAN_Top"").CellAmount
    'Calculated the difference betwee QRM and Model
    Dim Variance As Decimal = QRMTotal - ModelTotal

    'If variance isn't equal to 0 then log it as a month to display in the rule
    If Variance <> 0 Then
    If i = 0 Then
    strErrorAcct = (""In ""& Month.Member.Name & "" the Model Total and QRM Total Do Not Tie."")
    Else
    strErrorAcct = strErrorAcct & vbCrLf & (""In ""& Month.Member.Name & "" the Model Total and QRM Total Do Not Tie."")
    End If
    i = (i+1)
    End If
    'BRApi.ErrorLog.LogMessage(si, Month.Member.Name & CurLoanOutstanding.ToString)
    Next
    End If

    'If we didn't log any errors then show that the rule passed and display the correct error message.
    If i = 0 Then
    args.ConfirmationRuleArgs.DisplayValue = 0
    args.ConfirmationRuleArgs.Info1 = api.Pov.Entity.Description.ToString
    args.ConfirmationRuleArgs.Info2 = ""All Months QRM Total and Model Total Equal""
    Return True
    'Show the number of the logs where we said it failed and display the correct error message.
    Else
    args.ConfirmationRuleArgs.DisplayValue = i
    args.ConfirmationRuleArgs.Info1 = api.Pov.Entity.Description.ToString
    args.ConfirmationRuleArgs.Info2 = (""There are "" & i.ToString & "" Months That the Model Total and QRM Total Do Not Tie."")
    args.ConfirmationRuleArgs.Info3 = (StrErrorAcct)
    Return False
    End If