Max Calculation in CV

Thanks. I figured it out, I just made my column |My_Parameter|M12 and it worked since the column will always use M12 as the final month.

Thanks for all the knowledge gain you gave me.

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?

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.

Hi Henning,

Totally get it. I can get values, but that's only when I put a time value in, I'm still really stumped on how to get it dynamically based off whatever time or a parameter I use. I'm not sure if it has to do with the expansion I put in or what?

Hi, may I ask you for your rule when it is working and when it is not working as you are expecting? If you could post those two here, that would be helpful. 

Hello,

I used your formula you stated above and that worked fine.

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()

Then I tried making it dynamic (only using a parameter that this is being used since I couldn't get it with any time) and I couldn't get it to work. Just kept erroring or not showing the correct value.

Dim dAmount_1 As Decimal = api.Data.GetDataCell("T#[YearPrior1(|!Param_Select_Time_Year!|)][Period(M12)]:U8#None").CellAmount
Dim dAmount_2 As Decimal = api.Data.GetDataCell("T#[|!Param_Select_Time_Year!|.base]:U8#None").CellAmount

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

Return myArray.Max()

Basically it takes the year selected and M12 of the past year (i.e, if 2024 is selected, it also looks at 2023M12). I'm also using my parameter.base. (Does expansions play an issue to this?) Then it looks for the max value for that account.

Thank you, that makes it clear!

The first row does not work because YearPrior1() needs a full time string in the brackets I believe, e.g. YearPrior1(|POVTime|) or YearPrior1(|WFTime|), not just a year, unless the input frequency is yearly. But I have not tested that. Also the Period() function needs the same parameter I believe, e.g. T#YearPrior1(|POVTime|)Period(|POVTime|).

Not sure why you'd want to hard-code "M12"? I thought you need to flexibly pull the previous 12 periods for you max amount? To only use the last period of the previous year, T#PovLastInYearPrior1 can be used.

Dim dAmount_1 As Decimal = api.Data.GetDataCell("T#[YearPrior1(|!Param_Select_Time_Year!|)][Period(M12)]:U8#None").CellAmount

This one can definitely not work.

Dim dAmount_2 As Decimal = api.Data.GetDataCell("T#[|!Param_Select_Time_Year!|.base]:U8#None").CellAmount

GetDataCell needs single a specified data point to get the data cell from. By using ".base", you are telling the system to look for (I assume) 12 different data cells in 12 different data units.

You will either have to loop through each period, or just do it manually. As looping does not add any performance benefits in this case, I would in your case just use the simple way, even if it means a few more rows of code.

Dim dAmount_1 As Decimal = api.Data.GetDataCell("T#PovPrior1:U8#None").CellAmount
Dim dAmount_2 As Decimal = api.Data.GetDataCell("T#PovPrior2:U8#None").CellAmount
Dim dAmount_3 As Decimal = api.Data.GetDataCell("T#PovPrior4:U8#None").CellAmount
'...add rows for amounts 4-11...
Dim dAmount_12 As Decimal = api.Data.GetDataCell("T#PovPrior12:U8#None").CellAmount

With this you will get the amounts for the previous 12 periods into your array and the function will return the maximum value out of those 12. So in your CV which you shared a screenshot of, you will want to select T#2023M6 to use this for your dynamic member. The dynamic member will use that time and then return the max amount of the previous 12 periods, regardless of the year (i.e. dAmount_6 will be getting the amount from December 2022 etc.). 

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!