12-02-2022 02:28 PM - last edited on 05-02-2023 10:11 AM by JackLacava
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!
Solved! Go to Solution.
12-05-2022 06:01 AM - last edited on 12-22-2022 10:26 AM by JackLacava
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.
12-02-2022 08:54 PM - edited 12-02-2022 09:14 PM
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-t....
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.
12-05-2022 06:01 AM - last edited on 12-22-2022 10:26 AM by JackLacava
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.
12-15-2022 10:22 AM
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?
12-05-2022 10:59 AM
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).
01-20-2023 02:02 PM
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?