Best Practice To Reproduce MultiRound Functions In GCP

[TrifactaUsers]

Hello Steve & Community,

I've been playing with the Round and Floor functions in GCP, but haven't quite resolved some basic math that I'm trying to reproduce from Visual Basic scripts that plug into Access.

What I want to do, is to simply create resolutions for these requests:

For column RD1, when column MRHG is between 25 and 499.99, take MRHG + 25% rounded up to the nearest 1.87.

So far, I've got this case statement, and it's rounding up, but not to the nearest 1.87.:

set col: RD1 value: CASE([(MRHG <= 499.99) && (MRHG >= 25),ROUND(MRHG * 1.25, 1.87),RD1])

Here is the code I call on in VB:

Public Function multiRound(inVal As Double, roundTo As Variant, Optional roundDir As Integer = 0) As Double

   Select Case roundDir

      Case Is < 0:  multiRound = floor(inVal, roundTo)

      Case 0:     multiRound = RoundToNearest(inVal, roundTo)

      Case Is > 0:  multiRound = ceiling(inVal, roundTo)

    End Select

End Function

Here is the code in SQL for the RD values:

UPDATE [output data] SET [output data].RD1 = multiround([MRHG]*1.25,1.87,1), [output data].RD2 = ((multiround([MRHG]*1.25,1.87,1))/1.87), [output data].RD3 = multiround([MRHG]*1.5,1.87,1), [output data].RD4 = ((multiround([MRHG]*1.5,1.87,1))/1.87), [output data].RD5 = multiround([MRHG],1.87,1), [output data].RD6 = ((multiround([MRHG],1.87,1))/1.87)

WHERE ((([output data].MRHG) Between 25 And 499.99));

Thank you!

/steven

[Trifacta_Alumni]

I'm mildly surprised that ROUND works at all with a float as the second argument. The second argument is the number of digits to which to round, and expects an integer, e.g., ROUND(PI(),4) resolves to 3.1416. I know this doesn't answer your question, but I hope it provides a tiny bit of clarity anyway.

[TrifactaUsers]

Interesting! The second argument is simply the number of digits. Well, this is helpful to an extent, but no, does not solve the problem. :)

[Trifacta_Alumni · Accepted solution]

Just to be clear: what you want to do is to round to the nearest multiple of 1.87, correct? If so, might this be what you are looking for?

This uses the CEILING function to "round" as desired. Note that the term to search when adding a new step is simply 'new', which maps to 'New Formula'.

Let me know if this helps.

Best,

Nathanael