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#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

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
    6 months ago

    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.

     

    • WillVitale's avatar
      WillVitale
      Contributor
      6 months ago

      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

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

        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

         

         

  • Max_Allen's avatar
    Max_Allen
    New Contributor
    5 months ago

    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

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

      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.