Thanks Nicolas, it's a long formula, first the error messages:
Then the formula:
If (api.Cons.IsLocalCurrencyforEntity() And Not api.Entity.HasChildren()) Then
Dim CurrentScenario As String = api.Pov.Scenario.Name
Dim StartMonthID As Integer = api.Scenario.GetWorkflowStartTime(api.Pov.Scenario.MemberId)
Dim StartMonth As String = api.Time.GetNameFromId(StartMonthID)
Dim CurTime As String = api.Pov.Time.Name
Dim currMonthNr As Integer = api.Time.GetPeriodNumFromId(api.Pov.time.MemberId)
' api.LogMessage(StartMonth & " " & CurTime)
Dim Source1a As String = Nothing
Dim Destination1a As String = Nothing
Dim Source1b As String = Nothing
Dim Destination1b As String = Nothing
Dim Source1c As String = Nothing
Dim Destination1c As String = Nothing
Dim Source1d As String = Nothing
Dim Destination1d As String = Nothing
Dim Source2a As String = Nothing
Dim Destination2a As String = Nothing
Dim Source2b As String = Nothing
Dim Destination2b As String = Nothing
Dim Source2c As String = Nothing
Dim Destination2c As String = Nothing
Dim Source2d As String = Nothing
Dim Destination2d As String = Nothing
Dim Source3 As String = Nothing
Dim Destination3 As String = Nothing
Dim Source4 As String = Nothing
Dim Destination4 As String = Nothing
Dim Source5 As String = Nothing
Dim Destination5 As String = Nothing
Dim Source6a As String = Nothing
Dim Destination6a As String = Nothing
Dim Source6b As String = Nothing
Dim Destination6b As String = Nothing
Dim Source7a As String = Nothing
Dim Destination7a As String = Nothing
Dim Source7b As String = Nothing
Dim Destination7b As String = Nothing
Dim Source7c As String = Nothing
Dim Destination7c As String = Nothing
Dim Source8 As String = Nothing
Dim Destination8 As String = Nothing
Dim Source9 As String = Nothing
Dim Destination9 As String = Nothing
Dim Source10 As String = Nothing
Dim Destination10 As String = Nothing
Dim Formula As String = Nothing
'================= BDGM MODEL SCENARIO LOGIC FOR FIRST PERIOD ONLY ========================================================
' For Scenario BDGM - the sources for the first period is seeded from a predefined Model scenario and time period
' or alternatively it is source from the September values (so M9 or Prior4) of the ACTUAL_Ops scenario
' Relevant seed scenario and time period to be updated by BEN
If CurrentScenario = "BDGM" Then
If currMonthNr = 1 Then
'For sources 1a to 2d the Opening Stock Vols and Values are seeded from a predefined Model scenario and Time period, or Sep (M9) from ACTUAL_Ops
Source1a = "S#ACTUAL_Ops:T#povprior4:A#ClosingStockVol:O#Top"
Destination1a = "T#pov:A#VOL_O_StockALUM:O#Import"
Source1b = "S#ACTUAL_Ops:T#povprior4:A#ClosingStockVol:O#Top"
Destination1b = "T#pov:A#VOL_O_StockWIP:O#Import"
Source1c = "S#ACTUAL_Ops:T#povprior4:A#ClosingStockVol:O#Top"
Destination1c = "T#pov:A#VOL_O_StockFG:O#Import"
Source1d = "S#ACTUAL_Ops:T#povprior4:A#ClosingStockVol:O#Top"
Destination1d = "T#pov:A#VOL_O_StockTR:O#Import"
Source2a = "S#Actual_OPS:T#povprior4:A#StockFeedstockCost:O#Top"
Destination2a = "T#pov:A#VAL_O_StockALUM:O#Import"
Source2b = "S#Actual_OPS:T#povprior4:A#ClosingStockValue:O#Top"
Destination2b = "T#pov:A#VAL_O_StockWIP:O#Import"
Source2c = "S#Actual_OPS:T#povprior4:A#ClosingStockValue:O#Top"
Destination2c = "T#pov:A#VAL_O_StockFG:O#Import"
Source2d = "S#Actual_OPS:T#povprior4:A#ClosingStockValue:O#Top"
Destination2d = "T#pov:A#VAL_O_StockTR:O#Import"
'Source & Destination 3 to 7c only relates to the Price calcs for first period of the BDGM scenario being seeded from a predefined scenario and Time Period,
' or Actual_OPS scenario fixed period M9 Sep, therefater for all other periods its calculated On the member account
Source3 = "S#Actual_OPS:T#povprior4:A#PRICE_StockCloseU6:O#Top"
Destination3 = "T#pov:A#PRICE_StockCloseU6:O#Import"
Source4 = "S#Actual_OPS:T#povprior4:A#PRICE_StockFeedstock:O#Top"
Destination4 = "T#pov:A#PRICE_StockFeedstock:O#Import"
Source5 = "S#Actual_OPS:T#povprior4:A#PRICE_StockFreightFS:O#Top"
Destination5 = "T#pov:A#PRICE_StockFreightFS:O#Import"
Source6a = "S#Actual_OPS:T#povprior4:A#PRICE_StockICmuFS:O#Top"
Destination6a = "T#pov:A#PRICE_StockICmuFS:O#Import"
Source6b = "S#Actual_OPS:T#povprior4:A#PRICE_StockICmuWIP:O#Top"
Destination6b = "T#pov:A#PRICE_StockICmuWIP:O#Import"
Source7a = "S#Actual_OPS:T#povprior4:A#PRICE_StockFSOther:O#Top"
Destination7a = "T#pov:A#PRICE_StockFSOther:O#Import"
Source7b = "S#Actual_OPS:T#povprior4:A#PRICE_StockWIPother:O#Top"
Destination7b = "T#pov:A#PRICE_StockWIPother:O#Import"
Source7c = "S#Actual_OPS:T#povprior4:A#PRICE_StockFGother:O#Top"
Destination7c = "T#pov:A#PRICE_StockFGother:O#Import"
'In the case of VAL_O_StockALUMFreight in first month of BDGM scenario, its sourced from a predefined Model scenario and Time period and account, or Actual_Ops StockPurchFreightCost period M9 Sep
Source8 = "S#Actual_OPS:T#povprior4:A#StockPurchFreightCost:O#Top:U8#LC"
Destination8 = "T#pov:A#VAL_O_StockALUMFreight:O#Import:U8#LC"
'In the case of StockICmarkupCost in the first month of BDGM forecast, its sourced from Actual_OPS account StockICmarkupCost and fixed period M9 Sep, and the result is used in IC Profit Elim price calc
'on account StockICmarkupCost2 for Trading UD5 PC's and account StockICmarkupCost3 for Non-Trading UD5 PC's
'For Fin Goods on Non-Trading UD5 PC's its based on a constant unit price calculated in Actual_OPS on PRICE_StockICmuFG in USD for UD5 Text 2 thats not Trading)
Source9 = "S#Actual_OPS:T#povprior4:A#StockICmarkupCost:F#Final:O#Top:U8#USD"
Destination9 = "T#pov:A#StockICmarkupCost2:F#Calc:O#Import:U1#220F:U8#USD"
Source10 = "S#Actual_OPS:T#povprior4:A#PRICE_StockICmuFG:F#Final:O#Top:U8#USD"
Destination10 = "T#pov:A#PRICE_StockICmuFG:F#Calc:O#Import:U1#220F:U8#USD"
Formula = $"{Destination3}=RemoveZeros({Source3})"
api.data.calculate(Formula, UD6filter:="U6#InputMats.Base.Where(Text1 Startswith ALUMINA)")
Formula = $"{Destination4}=RemoveZeros({Source4})"
api.data.calculate(Formula, UD6filter:="U6#InputMats.Base.Where(Text1 Startswith ALUMINA)")
Formula = $"{Destination5}=RemoveZeros({Source5})"
api.data.calculate(Formula, UD6filter:="U6#InputMats.Base.Where(Text1 Startswith ALUMINA)")
Formula = $"{Destination6a}=RemoveZeros({Source6a})"
api.data.calculate(Formula, UD6filter:="U6#InputMats.Base.Where(Text1 Startswith ALUMINA)")
Formula = $"{Destination6b}=RemoveZeros({Source6b})"
api.data.calculate(Formula, UD4filter:="U4#Product.Base.Where(Text1 Startswith WIP)")
Formula = $"{Destination7a}=RemoveZeros({Source7a})"
api.data.calculate(Formula, UD6filter:="U6#InputMats.Base.Where(Text1 Startswith ALUMINA)")
Formula = $"{Destination7b}=RemoveZeros({Source7b})"
api.data.calculate(Formula, UD4filter:="U4#Product.Base.Where(Text1 Startswith WIP)")
Formula = $"{Destination7c}=RemoveZeros({Source7c})"
api.data.calculate(Formula, UD4filter:="U4#Product.Base.Where(Text1 Startswith FG)")
Formula = $"{Destination8}=RemoveZeros({Source8})"
api.data.calculate(Formula, UD6filter:="U6#InputMats.Base.Where(Text1 Startswith ALUMINA)")
Formula = $"{Destination9}=RemoveZeros({Source9})"
api.data.calculate(Formula, UD4filter:="U4#Product.Base.Where(Text1 = FG)", UD5Filter:="U5#Top.Base.Where(Text2 = Trading)")
Formula = $"{Destination10}=RemoveZeros({Source10})"
api.data.calculate(Formula, UD4filter:="U4#Product.Base.Where(Text1 = FG)", UD5Filter:="U5#Top.Base.Where(Text2 <> Trading)")
Else
'================= BDGM MODEL SCENARIO LOGIC FOR PERIODS 2 ONWARDS ========================================================
Source1a = "T#povprior1:A#VOL_C_StockALUM:O#Top"
Destination1a = "T#pov:A#VOL_O_StockALUM:O#Import"
Source1b = "T#povprior1:A#VOL_C_StockWIP:O#Top"
Destination1b = "T#pov:A#VOL_O_StockWIP:O#Import"
Source1c = "T#povprior1:A#VOL_C_StockFG:O#Top"
Destination1c = "T#pov:A#VOL_O_StockFG:O#Import"
Source1d = "T#povprior1:A#VOL_C_StockTR:O#Top"
Destination1d = "T#pov:A#VOL_O_StockTR:O#Import"
Source2a = "T#povprior1:A#VAL_C_StockALUM:O#Top:F#Final"
Destination2a = "T#pov:A#VAL_O_StockALUM:O#Import:F#ImportBW"
Source2b = "T#povprior1:A#VAL_C_StockWIP:O#Top:F#Final"
Destination2b = "T#pov:A#VAL_O_StockWIP:O#Import:F#ImportBW"
Source2c = "T#povprior1:A#VAL_C_StockFG:O#Top:F#Final"
Destination2c = "T#pov:A#VAL_O_StockFG:O#Import:F#ImportBW"
Source2d = "T#povprior1:A#VAL_C_StockTR:O#Top:F#Final"
Destination2d = "T#pov:A#VAL_O_StockTR:O#Import:F#ImportBW"
End If
Formula = $"{Destination1a}=RemoveZeros({Source1a})"
api.data.calculate(Formula, UD6filter:="U6#InputMats.Base.Where(Text1 Startswith ALUMINA)")
Formula = $"{Destination1b}=RemoveZeros({Source1b})"
api.data.calculate(Formula, UD4filter:="U4#Product.Base.Where(Text1 Startswith WIP)")
Formula = $"{Destination1c}=RemoveZeros({Source1c})"
api.data.calculate(Formula, UD4filter:="U4#Product.Base.Where(Text1 Startswith FG)", UD5filter:="U5#ProfitCenterbyFunction.Base.Where(Text2 doesnotcontain Trading)")
Formula = $"{Destination1d}=RemoveZeros({Source1d})"
api.data.calculate(Formula, UD4filter:="U4#Product.Base.Where(Text1 Startswith FG)", UD5filter:="U5#ProfitCenterbyFunction.Base.Where(Text2 = Trading)")
Formula = $"{Destination2a}=RemoveZeros({Source2a})"
api.data.calculate(Formula, UD6filter:="U6#InputMats.Base.Where(Text1 Startswith ALUMINA)")
Formula = $"{Destination2b}=RemoveZeros({Source2b})"
api.data.calculate(Formula, UD4filter:="U4#Product.Base.Where(Text1 Startswith WIP)")
Formula = $"{Destination2c}=RemoveZeros({Source2c})"
api.data.calculate(Formula, UD4filter:="U4#Product.Base.Where(Text1 Startswith FG)", UD5filter:="U5#ProfitCenterbyFunction.Base.Where(Text2 doesnotcontain Trading)")
Formula = $"{Destination2d}=RemoveZeros({Source2d})"
api.data.calculate(Formula, UD4filter:="U4#Product.Base.Where(Text1 Startswith FG)", UD5filter:="U5#ProfitCenterbyFunction.Base.Where(Text2 = Trading)")
Formula = $"{Destination8}=RemoveZeros({Source8})"
api.data.calculate(Formula, UD6filter:="U6#InputMats.Base.Where(Text1 Startswith ALUMINA)")
End If
End If
