06-25-2024 04:55 PM
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
Solved! Go to Solution.
07-04-2024 03:48 AM
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()
06-26-2024 12:39 AM
Hi, I think you will need a dynamically calculated member or a custom GetDataCell expression.
I would go the dynamic member route, e.g. by setting up a UD8 member U8#MaxPrevious12Periods. You will pull the time and account (etc.) from the POV, and then check the values for each of those intersections of the previous 12 months (assuming this is your time span) and then just return the maximum value out of those. Please note that this will not be super performant and I would recommend using this in smaller cube view only as you will be pulling data from multiple data units.
06-26-2024 12:53 PM
Hi Henning,
Ok, I will give this a shot. It's only 1 account, so I don't think the performance should be bad. Since I don't make Ud8's often, do you know the formula I would use?
Will
06-27-2024 02:15 AM
Hi, as always many roads lead to Rome.
I would create a loop over the previous 11 periods and pull the data using GetDataCell such as this:
Dim dAmount As Decimal = api.Data.GetDataCell("A#Accountxyz").CellAmount
Add the amounts into an array and then pull the maximum amount out of that array and return the max amount using something like this:
Dim maxAmount = myAmountArray.Max()
Return maxAmount
07-02-2024 02:38 PM
Hi Henning,
if I want it to be different accounts (dynamic) how would I go about setting that up?
Sorry for all the questions, I don't make these really, so learning as I go.
07-04-2024 03:48 AM
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()
07-17-2024 05:54 PM
Now if I want the formula to be dynamic for the account, would I just exclude the account?
07-18-2024 02:50 AM
Exactly (provided your cube view is set up accordingly)!
07-18-2024 11:24 AM
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?
07-19-2024 02:26 AM
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.
07-22-2024 02:39 PM
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?
07-23-2024 12:42 AM
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.
07-23-2024 11:20 AM
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.
07-24-2024 03:21 AM
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.).
07-30-2024 12:27 PM
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
07-31-2024 03:22 AM
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!
07-31-2024 11:23 AM
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.
08-08-2024 07:54 PM
Thank you!
I created a DynamicCalc member in my application using the above formula and it worked!
Now I'm trying to create another formula that will give me the time that the max value happened. Any suggestions?
Max
08-09-2024 01:12 AM
Hi, you can use e.g. a multi-column list box or create your own public class instead of an array in which you store the amount and the time. Multidimensional arrays exist as well, but I have not tested how the max function works with that.
Or, probably the easiest (though not the "prettiest" in coding terms) solution is using the IndexOf function to find the max amount position in your array and derive the period from that. If you google "IndexOf Array VB.Net" you should find enough examples to get you started.
In essence, there are many ways to go about it and I do not have the single golden path ready in this case.