Absolute Value in a UD8 Formula

[WillVitale]

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 instead of being 0. 

Here is the current formula I'm using

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 for the help!

Will

[TheJonG · Accepted solution]

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

[TheJonG · Accepted solution]

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

[WillVitale]

Hi Jon,

Yes, it did. Is there a way though to recall the original value from it? 

Ex. If -1,000,000 was the largest value, currently the UD8 gives me a result of 1M. Is there a way to make it recall it back to the original -1M instead?

Will

[TheJonG]

I'm not sure why you would need to do that. You have the original amount from the dAmount_1 variable and then you can set the absolute value to a new variable. 

[WillVitale]

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]

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 · Accepted solution]

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)