Hi there - Im looking to add a formula to my columns located in the cube view. I need it to pull global time + remaining target months. This involves two scenarios, so S#Actual and S#Target_FY24. So If my POV global time is July, I need the formula to take (7 months of actuals using S#Actual) + (5 remaining months of target using S#target_FY24). I also need it to be dynamic so I dont have to touch each month, so when the POV gets switched to August, it would be (8 months of actuals + 4 months of target)
I have always done this using XFBR strings. So you will create one XFBR string that will pull your Actual periods (e.g. ActPeriods as shown below) that you can leverage in one column, and another XFBR string (or the inverse of the first one) that will pull forecast periods.
So one column in your report will use an XFBR string that will pull back "ActPeriods" and then a second column with an XFBR string that will pull back "FcstPeriods".
Below is example of the XFBR strings. In my case I used the scenario name to determine Actual vs Forecast periods, but you could also you use the "NoInputPeriods" property.
If args.FunctionName.XFEqualsIgnoreCase("ActPeriods") Then
' XFBR(_XFBRStrings, ActPeriods, CurrScenario = |WFScenario|, wfYear= |WFYear|)
Dim CurrScenario As String = args.NameValuePairs.XFGetValue("CurrScenario")
Dim wfYear As Integer = args.NameValuePairs.XFGetValue("wfYear")
If CurrScenario.XFContainsIgnoreCase ("FCST") Then
Dim scnPeriod As Integer = RIGHT(CurrScenario,2)
' brapi.ErrorLog.LogMessage(si, "Scenario = " & CurrScenario & " Year = " & wfYear & " scnPeriod = " & scnPeriod)
If scnPeriod < 2 Then
Return "T#2010"
Else
Return "T#" & wfYear & "M" & scnPeriod - 1 & ".AllPriorInYearInclusive"
End If
Else
Return "T#" & wfYear & "M12.AllPriorInYearInclusive"
End If
End If
If args.FunctionName.XFEqualsIgnoreCase("FCSTPeriods") Then
' XFBR(_XFBRStrings, ActPeriods, CurrScenario = |WFScenario|, wfYear= |WFYear|)
Dim CurrScenario As String = args.NameValuePairs.XFGetValue("CurrScenario")
Dim wfYear As Integer = args.NameValuePairs.XFGetValue("wfYear")
If CurrScenario.XFContainsIgnoreCase ("FCST") Then
Dim scnPeriod As Integer = RIGHT(CurrScenario,2)
' brapi.ErrorLog.LogMessage(si, "Scenario = " & CurrScenario & " Year = " & wfYear & " scnPeriod = " & scnPeriod)
Return "T#" & wfYear & "M" & scnPeriod & ".AllNextInYearInclusive"
Else
Return "T#" & wfYear
End If
End If
