Forum Discussion

ZubairCSC's avatar
ZubairCSC
New Contributor II
2 years ago

Cash Flow Opening balance for MTD and YTD columns



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!
Cube View
  • EricOsmanski's avatar
    EricOsmanski
    2 years ago

    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
  • EricOsmanski's avatar
    EricOsmanski
    Valued Contributor
    2 years ago

    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
    • ZubairCSC's avatar
      ZubairCSC
      New Contributor II
      2 years ago

      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.