Skip to main content
B
bisenhart
August 6, 2024

Cube view formula

  • August 6, 2024
  • 2 replies
  • 0 views

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)

2 replies

T
T_Kress
August 6, 2024

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.


T_Kress_0-1722971932828.png

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

 

 

 

 

 

D
db_pdx
August 6, 2024

This can also be done with member expansions and a basic GetDataCell.

I'd recommend putting your global time in the fixed pov (|GlobalTime|) and then referencing that in your report. The GetDataCell would be:

GetDataCell(T#|CVTime|:S#Actual:V#YTD + T#|CVTime|:S#Target_FY24:V#RestOfYear):Name(Total)

Cheers,  -db

Terms & ConditionsAccessibility statement