Forum Discussion

camagruder's avatar
camagruder
New Contributor III
3 years ago

Member Formula - Min/Max Calculations

I need some help building out the following two calculations on stored member formulas. They would need to run at every UD.

1) = MIN((A#Account1 - 4.5%), (A#Account2 - 6%), (A#Account3 - 8%))

2) = MAX((A#Account1 - Account2 - Account3 - Account4),0)

Thanks so much!

Finance
  • ChrisLoran's avatar
    ChrisLoran
    3 years ago

    Depends a lot on my/our interpretation of the requirements as stated here.
    If you want to find which mix of UD1-UD8 is the one mix that yields the MIN/MAX amounts with those accounts, then it should be possible to open a single databuffer which is just pre-filtered on those four accounts (using GetDataBufferUsingFormula(FilterMembers()) if necessary)
    Then when iterating through the databuffer, you get a DataBufferCell, and you can first test if
    DataBufferCell.DataBufferCellPk.AccountId = [ Account ID of A#Account1 ]
    If found then you grab the DataBufferCell as a variable, then set DataBufferCell.DataBufferCellPk.AccountId to the accountID of one of the other accounts (e.g. Account2), and then immediately retrieve the corresponding cell for Account2 (with the same UDs) from the same databuffer , like this :

    '-------------------------------------------

Dim myBuf As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(A#All,[A#SomeParent.Base])")
Dim i_AcctId_Acct1 As Integer = api.Members.GetMemberId(DimTypeId.Account,"Account1")
Dim i_AcctId_Acct2 As Integer = api.Members.GetMemberId(DimTypeId.Account,"Account2")
Dim i_AcctId_Acct3 As Integer = api.Members.GetMemberId(DimTypeId.Account,"Account3")

For Each myCell In myBuf.DataBufferCells.Values
  If myCell.DataBufferCellPk.AccountId = i_AcctId_Acct1 AndAlso Not(myCell.CellStatus.IsNoData) Then
    Dim Acct2CellPk As New DataBufferCellPk(myCell.DataBufferCellPk)
    Acct2CellPk.AccountId = i_AcctId_Acct2
    Dim cellForAcct2 As DataBufferCell = myBuf.GetCell(si, Acct2CellPk)
    Dim Acct3CellPk As New DataBufferCellPk(myCell.DataBufferCellPk)
    Acct3CellPk.AccountId = i_AcctId_Acct3
    Dim cellForAcct3 As DataBufferCell = myBuf.GetCell(si, Acct3CellPk)

' --- tests Acct1 - Acct2 - Acct3 , for this specific combination of UD1-8, and check   if cells exist for other accts
' --- assume if no cell exists for Acct2/Acct3 then assume they are zero. You might want to have a different behaviour.
    Dim Acct1Amount As Decimal = myCell.CellAmount
    Dim Acct2Amount As Decimal = If(cellForAcct2 Is Nothing, 0, cellForAcct2.CellAmount)
    Dim Acct3Amount As Decimal = If(cellForAcct3 Is Nothing, 0, cellForAcct3.CellAmount)

    Dim testResult As Decimal = Acct1Amount - Acct2Amount - Acct3Amount
'--- compare the testResult with previous min/max amounts --
'--- put your own code here to update your own variables accordingly --
  End If
Next

'--------------------------------------------

    Remember a DataBuffer is basically a dictionary with a DataBufferCellPk as it's key. So you can look up cells very quickly once you have a DataBufferCellPk object that it initialized with all the member IDs for each dimension type.

    So you should be able to get a DataBufferCell for Account1/Account2/Account3/Account4 , each which have the same corresponding UDs. Once you have 4 variables of type DataBufferCell then you can do the min/max math directly on the cellAmounts, and record the min/max results before looping to the next iteration.

  • ChrisLoran's avatar
    ChrisLoran
    Valued Contributor
    3 years ago

    Depends a lot on my/our interpretation of the requirements as stated here.
    If you want to find which mix of UD1-UD8 is the one mix that yields the MIN/MAX amounts with those accounts, then it should be possible to open a single databuffer which is just pre-filtered on those four accounts (using GetDataBufferUsingFormula(FilterMembers()) if necessary)
    Then when iterating through the databuffer, you get a DataBufferCell, and you can first test if
    DataBufferCell.DataBufferCellPk.AccountId = [ Account ID of A#Account1 ]
    If found then you grab the DataBufferCell as a variable, then set DataBufferCell.DataBufferCellPk.AccountId to the accountID of one of the other accounts (e.g. Account2), and then immediately retrieve the corresponding cell for Account2 (with the same UDs) from the same databuffer , like this :

    '-------------------------------------------

Dim myBuf As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(A#All,[A#SomeParent.Base])")
Dim i_AcctId_Acct1 As Integer = api.Members.GetMemberId(DimTypeId.Account,"Account1")
Dim i_AcctId_Acct2 As Integer = api.Members.GetMemberId(DimTypeId.Account,"Account2")
Dim i_AcctId_Acct3 As Integer = api.Members.GetMemberId(DimTypeId.Account,"Account3")

For Each myCell In myBuf.DataBufferCells.Values
  If myCell.DataBufferCellPk.AccountId = i_AcctId_Acct1 AndAlso Not(myCell.CellStatus.IsNoData) Then
    Dim Acct2CellPk As New DataBufferCellPk(myCell.DataBufferCellPk)
    Acct2CellPk.AccountId = i_AcctId_Acct2
    Dim cellForAcct2 As DataBufferCell = myBuf.GetCell(si, Acct2CellPk)
    Dim Acct3CellPk As New DataBufferCellPk(myCell.DataBufferCellPk)
    Acct3CellPk.AccountId = i_AcctId_Acct3
    Dim cellForAcct3 As DataBufferCell = myBuf.GetCell(si, Acct3CellPk)

' --- tests Acct1 - Acct2 - Acct3 , for this specific combination of UD1-8, and check   if cells exist for other accts
' --- assume if no cell exists for Acct2/Acct3 then assume they are zero. You might want to have a different behaviour.
    Dim Acct1Amount As Decimal = myCell.CellAmount
    Dim Acct2Amount As Decimal = If(cellForAcct2 Is Nothing, 0, cellForAcct2.CellAmount)
    Dim Acct3Amount As Decimal = If(cellForAcct3 Is Nothing, 0, cellForAcct3.CellAmount)

    Dim testResult As Decimal = Acct1Amount - Acct2Amount - Acct3Amount
'--- compare the testResult with previous min/max amounts --
'--- put your own code here to update your own variables accordingly --
  End If
Next

'--------------------------------------------

    Remember a DataBuffer is basically a dictionary with a DataBufferCellPk as it's key. So you can look up cells very quickly once you have a DataBufferCellPk object that it initialized with all the member IDs for each dimension type.

    So you should be able to get a DataBufferCell for Account1/Account2/Account3/Account4 , each which have the same corresponding UDs. Once you have 4 variables of type DataBufferCell then you can do the min/max math directly on the cellAmounts, and record the min/max results before looping to the next iteration.

    • camagruder's avatar
      camagruder
      New Contributor III
      3 years ago

      Thank you so much!  I was able to use your code to get this working in both calculations except where we need to look at Origin = Top to evaluate the Min/Max.  How would I update the filter to only include O#Top?

  • JackLacava's avatar
    JackLacava
    Honored Contributor
    3 years ago

    I would add to the solution above that FilterMembers accepts multiple parameters on the same dimension, so one should be able to do FilterMembers(A#All, A#Account1, A#Account2, A#Account3, A#Account4).

    • camagruder's avatar
      camagruder
      New Contributor III
      2 years ago

      I was able to use your code to get the filter working in both calculations except where we need to look at Origin = Top to evaluate the Min/Max.  How would I update the Filter to only include O#Top?

  • TonyToniTone's avatar
    TonyToniTone
    Contributor II
    3 years ago

    I don't have a solution for this. However, I can point you in a direction. Since MIN and MAX functions do not work on api.Data.Calculate functions, my suggestion would be to get all the individual data cells in each data buffer to then sort in order to get MIN and MAX. Because you only have 1 dimension member for your data buffer i.e. Account1, each data buffer may have tens of thousands of data cells to collect in order to sort. Once you get the data cells in the data buffers, I believe you can use a LINQ technique to find the MIN and MAX in a query result using Order By clause and Ascending and Descending keywords. It looks like in item 1, you will need get the data cells from 3 individual data buffers before you can perform the MIN function. Item 2, looks like you will have 4 individual data buffers with the data cells for MAX. Here is a link to a Microsoft resource to learn more about different LINQ techniques https://learn.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/linq/how-to-find-the-minimum-or-maximum-value-in-a-query-result.

    If you do not get additional ideas for this solution or you run into a roadblock building these calculations, I would suggest submitting a ticket to OneStream Support and discuss this with the AAS team to see if they can help with a solution to this.