Forum Discussion

osdevadmin's avatar
osdevadmin
New Contributor III
9 months ago

"YTD" like calculation but for custom periods

I am writing a member formula/dynamic BR where i have to get 'YTD" like cumulative members but instead get it from month 7 onwards i.e. if i want to get cumulative data from month 7 onwards, i wrote a very rudimentary code to do this and it works but checking if we have something better. Please let me know if there is something better:

If api.Pov.Cube.Name = "Ledger" Then
    If (api.Cons.IsCurrency()) Then
        Dim fiscalYearShort As Int16 = CInt(Left(api.Pov.Time.Name, 4))
        Dim periodLabel As String = api.Pov.Time.Name
        Dim periodIdentifier As Integer = api.Pov.Time.MemberId
        Dim periodSequence As Integer = api.Time.GetPeriodNumFromId(periodIdentifier)
        Dim filterString As String = "V#Periodic:A#IncStmt:F#EndBal:O#Top:U1#TotCC:U2#TotRegion:U3#TotDept:U4#TotLevel:U5#Reporting"
        Dim dataCellResult As DataCell = api.Data.GetDataCell(filterString)
        Dim cumulativeAmount As Decimal = Nothing
        
        If fiscalYearShort < 2023 Then
            Return Nothing
        ElseIf fiscalYearShort = 2023
            For monthIndex As Integer = 1 To 6
                cumulativeAmount += api.Data.GetDataCell("T#" & fiscalYearShort & "M" & monthIndex & ":" & filterString).CellAmount
            Next
            If periodSequence <= 6
                Return Nothing
            ElseIf periodSequence = 7
                Return api.Data.GetDataCell("V#Periodic:A#IncStmt:F#EndBal:O#Top:U1#TotCC:U2#TotRegion:U3#TotDept:U4#TotLevel:U5#Reporting")
            ElseIf periodSequence > 7
                Return api.Data.GetDataCell("V#YTD:A#IncStmt:F#EndBal:O#Top:U1#TotCC:U2#TotRegion:U3#TotDept:U4#TotLevel:U5#Reporting").CellAmount - cumulativeAmount
            End If
        End If
    End If
End If

 

  • db_pdx's avatar
    db_pdx
    Valued Contributor

    Does the out of the box V#RestOfYear meet your requirements?

    • osdevadmin's avatar
      osdevadmin
      New Contributor III

      Thanks db_pdx , for getting cumulative numbers till 2023M6 i also used V#YTD:T#2023M6 and it worked!

  • osdevadmin's avatar
    osdevadmin
    New Contributor III

    This is the code that gets cumulative number from specific month its very rough so don't like it:

      For monthIndex As Integer = 1 To 6
                    cumulativeAmount += api.Data.GetDataCell("T#" & fiscalYearShort & "M" & monthIndex & ":" & filterString).CellAmount