Forum Discussion

WillVitale's avatar
WillVitale
Contributor
6 months ago

Max Calculation in CV

Hello, I'm writing a cube view and our old report (not in OS) has a max value calculation in the columns that looks at the current years values for each entity. I tried writing a GetDataCell(Max(A#...
Cube View
Reports
  • Henning's avatar
    Henning
    6 months ago

    Here is a simple example, displaying the max amount of the prior two periods. I set up a UD8 member:

    ...which returns the max amount from my account 40000 from T#POVPrior1 and 2 using this member formula:

    Dim dAmount_1 As Decimal = api.Data.GetDataCell("A#40000:T#PovPrior1:U8#None").CellAmount
Dim dAmount_2 As Decimal = api.Data.GetDataCell("A#40000:T#PovPrior2:U8#None").CellAmount

Dim myArray As Decimal() = New Decimal(){dAmount_1, dAmount_2}

Return myArray.Max()

     

Henning's avatar
Henning
Valued Contributor II

Here is a simple example, displaying the max amount of the prior two periods. I set up a UD8 member:

...which returns the max amount from my account 40000 from T#POVPrior1 and 2 using this member formula:

Dim dAmount_1 As Decimal = api.Data.GetDataCell("A#40000:T#PovPrior1:U8#None").CellAmount
Dim dAmount_2 As Decimal = api.Data.GetDataCell("A#40000:T#PovPrior2:U8#None").CellAmount

Dim myArray As Decimal() = New Decimal(){dAmount_1, dAmount_2}

Return myArray.Max()

 

WillVitale's avatar
WillVitale
Contributor
6 months ago

Now if I want the formula to be dynamic for the account, would I just exclude the account?

  • Henning's avatar
    Henning
    Valued Contributor II
    6 months ago

    Exactly (provided your cube view is set up accordingly)!

    • WillVitale's avatar
      WillVitale
      Contributor
      6 months ago

      Awesome! Now if Time is in my cube view already as well, would it literally just be U8#None for both of the dim dAmounts?

      • Henning's avatar
        Henning
        Valued Contributor II
        6 months ago

        One of my personal goals here is to help people stand on their on feet (i.e. in OneStream). So I would like to encourage you to test this and play around with it. This - in my view - will be more helpful then me providing you with every last detail of the solution. ğŸ˜‰

        When you play around with the dimensions in the cube view and the dynamic member formula you should see how it is behaving and why. If you do not get it to work, happy to help you get there after a sufficient amount of self-testing.