osdevadmin
9 months agoNew Contributor III
"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