benmac360
2 years agoNew Contributor III
UD8 Dynamic Calc - Show Zeroes in Cube View column based on time period comparison
Hello - I created a dynamic UD8 calc for a cube view that shows 12 months of actual, forecast and budget periodic amounts. The calc is designed to show zeroes in the actual scenario column for a period if its POV time is greater than than the global time. I added it to the cube view and it works perfectly when I run it for 2024M12 - the actual columns are all zeroes, while it displays the budget and forecast numbers. When I run the CV for 2023M12, since our global time is still 2023M11 I'm expecting zeroes to be in the Actual column, but I'm still getting the opposite sign YTD "no data zero view" totals instead. Below is the formula in the UD8 member - let me know what changes I need to make. I appreciate the assistance.
Dim gbTimeName As String = BRApi.Workflow.General.GetGlobalTime(si)
Dim gbTimeID As Integer = BRApi.Finance.Time.GetIdFromName(si, gbTimeName)
Dim gbYear As Integer = BRApi.Finance.Time.GetYearFromId(si, gbTimeID)
'Get pov year
Dim pvYear As Integer = api.Time.GetYearFromId(api.Pov.Time.MemberId)
'Get global month
Dim objTimeMemberSubComponents As TimeMemberSubComponents = BRApi.Finance.Time.GetSubComponentsFromName(si, gbTimeName)
Dim gbMonthStr As String = objTimeMemberSubComponents.Month.ToString
Dim gbMonth As Integer = CInt(gbMonthStr)
'Get POV month
Dim pvTimeName As String = api.Pov.Time.MemberID
Dim pvobjTimeMemberSubComponents As TimeMemberSubComponents = BRApi.Finance.Time.GetSubComponentsFromName(si, pvTimeName)
Dim pvMonthStr As String = pvobjTimeMemberSubComponents.Month.ToString
Dim pvMonth As Integer = CInt(pvMonthStr)
Dim gbTime As Integer = gbYear & gbMonth
Dim pvTime As Integer = pvYear & pvMonth
If pvTime < gbTime Then
Return api.Data.CreateDataCellObject(0.0, False, False)
Else
Return api.Data.GetDataCell("U8#None").CellAmount
End If