Forum Discussion

WillVitale's avatar
WillVitale
Contributor
3 months ago

Absolute Value in a UD8 Formula

Hello, I currently have a dynamic formula that pulls the max value, but was asked to use that formula to get the absolute value that is also a negative the highest amount to pull that value as well...
Cube View
Reports
  • TheJonG's avatar
    TheJonG
    3 months ago

    Math.Abs(dAmount_1), Math.Abs(dAmount_2), etc should give you what you need.

  • WillVitale's avatar
    WillVitale
    3 months ago

    Was able to get it to work by the formula below

    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

' Create an array of the original amounts
Dim originalArray 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
}

' Create an array of absolute values
Dim absArray As Decimal() = originalArray.Select(Function(x) Math.Abs(x)).ToArray()

' Find the index of the maximum absolute value
Dim maxIndex As Integer = Array.IndexOf(absArray, absArray.Max())

' Return the original value that corresponds to the maximum absolute value
Return originalArray(maxIndex)

     

WillVitale's avatar
WillVitale
Contributor

Basically our tax team needs to pull the largest absolute value but from each interco that entity is assigned to, but then sums those largest values up together based off of tax type which I've created as a text6 field.

TheJonG's avatar
TheJonG
Contributor III
3 months ago

Ok I think I understand. Perhaps instead of using an array, you can put the values in a dictionary with the Name being the decimal name and the Value being the absolute value. Then you can pull the max value along with the name and then use the name to look up the original variable. 

  • WillVitale's avatar
    WillVitale
    Contributor
    3 months ago

    Was able to get it to work by the formula below

    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

' Create an array of the original amounts
Dim originalArray 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
}

' Create an array of absolute values
Dim absArray As Decimal() = originalArray.Select(Function(x) Math.Abs(x)).ToArray()

' Find the index of the maximum absolute value
Dim maxIndex As Integer = Array.IndexOf(absArray, absArray.Max())

' Return the original value that corresponds to the maximum absolute value
Return originalArray(maxIndex)