Skip to main content
April 5, 2024

"YTD" like calculation but for custom periods

  • April 5, 2024
  • 2 replies
  • 0 views

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

 

2 replies

April 5, 2024

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
April 8, 2024

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

April 9, 2024

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