Skip to main content
Z
ZubairCSC
Newcomer
November 2, 2023
Solved

Cash Flow Opening balance for MTD and YTD columns

  • November 2, 2023
  • 1 reply
  • 0 views

Cashflow.jpg

Hi All,

I  want to get Last month ending balance in my MTD column of cash flow and last year ending balance in my YTD column of cash flow as you can see in above picture.

Can anybody share with me the business rule? I have tried following business rule but it is capturing only at the end of the year balance.

Dim currView As ViewMember = ViewMember.GetItem(api.Pov.View.MemberPk.MemberId) 'Current view member ID
Dim priorQtr As Integer = api.Time.GetLastPeriodInPriorQuarter(api.Pov.Time.MemberId) 'Prior QTR member ID
Dim priorQtrName As String = api.Time.GetNameFromId(priorQtr) 'Prior QTR name
 
#End Region
 
' Bring in Beginning Cash and Ending Cash to the Cashflow hierarchy in Flow
If currView.Name = "YTD" Then
api.data.calculate("F#CF_BegCash" & Target & " = RemoveZeros(F#EndBal:T#PovPriorYearM12" & Source &")","A#CashTot.Base")
Else If currView.Name = "Periodic" Then
api.data.calculate("F#CF_BegCash" & Target & " = RemoveZeros(F#EndBal:T#PovPrior1" & Source &")","A#CashTot.Base")
Else If currView.Name = "MTD" Then
api.data.calculate("F#CF_BegCash" & Target & " = RemoveZeros(F#EndBal:T#PovPrior1" & Source &")","A#CashTot.Base")
End If
api.data.calculate("F#CF_EndCash" & Target & " = RemoveZeros(F#EndBal" & Source &")","A#CashTot.Base")
 
Please help!
Best answer by EricOsmanski

You will want to make this Flow Member to be Dynamic Calc. Here is an example from the CPM Blueprint with can be downlaoded from the Solution Exchange OpenPlace.

 

Dim currView As ViewMember = ViewMember.GetItem(api.Pov.View.MemberPk.MemberId) 'Current view member ID
Dim priorQtr As Integer = api.Time.GetLastPeriodInPriorQuarter(api.Pov.Time.MemberId) 'Prior QTR member ID
Dim priorQtrName As String = api.Time.GetNameFromId(priorQtr) 'Prior QTR name
 
If currView.Name = "YTD" Then
Return api.Data.GetDataCell("A#Cash:F#EndBal:T#PovPriorYearM12")
Else If currView.Name = "Periodic" Then
Return api.Data.GetDataCell("A#Cash:F#EndBal:T#PovPrior1")
Else If currView.Name = "QTD" Then
Return api.Data.GetDataCell("A#Cash:F#EndBal:T#[" + priorQtrName + "]")
Else
Return Nothing
End If

1 reply

E
OneStream Employee
EricOsmanskiOneStream EmployeeAnswer
OneStream Employee
November 2, 2023

You will want to make this Flow Member to be Dynamic Calc. Here is an example from the CPM Blueprint with can be downlaoded from the Solution Exchange OpenPlace.

 

Dim currView As ViewMember = ViewMember.GetItem(api.Pov.View.MemberPk.MemberId) 'Current view member ID
Dim priorQtr As Integer = api.Time.GetLastPeriodInPriorQuarter(api.Pov.Time.MemberId) 'Prior QTR member ID
Dim priorQtrName As String = api.Time.GetNameFromId(priorQtr) 'Prior QTR name
 
If currView.Name = "YTD" Then
Return api.Data.GetDataCell("A#Cash:F#EndBal:T#PovPriorYearM12")
Else If currView.Name = "Periodic" Then
Return api.Data.GetDataCell("A#Cash:F#EndBal:T#PovPrior1")
Else If currView.Name = "QTD" Then
Return api.Data.GetDataCell("A#Cash:F#EndBal:T#[" + priorQtrName + "]")
Else
Return Nothing
End If
    Z
    ZubairCSCAuthor
    Newcomer
    November 2, 2023

    Hi Eric,

    Thank you so much for your reply. I am new in the OneStream. How can I reference this DynamicCalc member in Cube view? I am trying to build this dynamic member in U8 dimension and trying to reference that U8 member in my Flow dimension  in my Cube View. I am attaching the picture. I m attaching U8 Dimension and business rule picture also.CV.jpgU8 Dimension.jpg

    Terms & ConditionsAccessibility statement