The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.

Forum Discussion

osdevadmin's avatar
osdevadmin
New Contributor III
2 years 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

 

3 Replies

  • 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