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#Account)) but it's giving me no values. I looked through Golf Stream but didn't find anything but a member ranking which doesn't help. I've also looked at the other Min/Max posts, but they're not exactly what I need.
Thanks,
Will
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()
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()
Hello,
I tried your formula you suggested and it's producing amounts, but I believe I'm doing something incorrect. Because when I chose the year in my CV (from my parameter, I would just select 2024) because it's the |MyParameter|.months, so is this causing the issue? Because I'm not selecting a month that now when I chose 2024, the T#POVPrior12 is actually looking at 2012 instead of June 2023?
Dim dAmount_1 As Decimal = api.Data.GetDataCell("T#Pov:U8#None").CellAmount
Dim dAmount_2 As Decimal = api.Data.GetDataCell("T#PovPrior1:U8#None").CellAmount
Dim dAmount_3 As Decimal = api.Data.GetDataCell("T#PovPrior2:U8#None").CellAmount
Dim dAmount_4 As Decimal = api.Data.GetDataCell("T#PovPrior3:U8#None").CellAmount
Dim dAmount_5 As Decimal = api.Data.GetDataCell("T#PovPrior4:U8#None").CellAmount
Dim dAmount_6 As Decimal = api.Data.GetDataCell("T#PovPrior5:U8#None").CellAmount
Dim dAmount_7 As Decimal = api.Data.GetDataCell("T#PovPrior6:U8#None").CellAmount
Dim dAmount_8 As Decimal = api.Data.GetDataCell("T#PovPrior7:U8#None").CellAmount
Dim dAmount_9 As Decimal = api.Data.GetDataCell("T#PovPrior8:U8#None").CellAmount
Dim dAmount_10 As Decimal = api.Data.GetDataCell("T#PovPrior9:U8#None").CellAmount
Dim dAmount_11 As Decimal = api.Data.GetDataCell("T#PovPrior10:U8#None").CellAmount
Dim dAmount_12 As Decimal = api.Data.GetDataCell("T#PovPrior11:U8#None").CellAmount
Dim dAmount_13 As Decimal = api.Data.GetDataCell("T#PovPrior12:U8#None").CellAmount
Dim myArray As Decimal() = New Decimal(){dAmount_1, dAmount_2, dAmount_3, dAmount_4, dAmount_5, dAmount_6, dAmount_7, dAmount_8, dAmount_9, dAmount_10, dAmount_11, dAmount_12, dAmount_13}
Return myArray.Max()
Thanks,
Will
Hi, yes, you are describing the expected behavior. PovPriorX does not refer to the columns in the cube view, it uses the Time of the (in your case) column it is executed on and returns the prior time periods 1, 2, 3, etc. If you use years, it returns prior years, if you use quarters, it will return quarters, etc. That is why I said "you will want to select T#2023M6 to use this for your dynamic member". Because in your cube view, you want to use months as periods.
So in this column, you cannot select T#2024:
I assume you are only selecting the year because this is what you want to see as a header. If that is the case, select it like this in your column member expansion filter:
T#2024M6:Name(|MFYear|)
This will pass the POV time 2024M6 to your dynamic calculation AND display "2024" as a column header in your cube view.
OneStream as well as licensed partners offer great trainings on the platform, reporting, rules etc. which explain the concepts in more detail than I can provide in this forum. I do recommend those!
