Rounding a number

[smiling_cheetah]

Hi community,

It looks like a silly question, but I couldn't find an answer and wasn't able to work it around myself, so:

How can I dynamically round a number, so that the final value is first two digits of the number plus 1 and the rest are zeroes:

Initial	After rounding
12345	13000
123	130
9987	10000
5	10

Would appreciate any help

Thanks

[sunny_talwar]

Try this

=Ceil(Initial, pow(10, RangeMax(Len(Initial)-2, 1)))

[sunny_talwar · Accepted solution]

Something like this in the script

Table:

LOAD *,

Ceil(Initial, pow(10, RangeMax(Len(Initial)-2, 1))) as [After rounding];

LOAD * INLINE [

    Initial

    12345

    123

    9987

    5

];

[smiling_cheetah]

Thanks Sunny, that's what I was looking for, but forgot to mention, that I'm using a complex expression as Initial:

=RangeMax(

max(aggr($(=FirstSortedValue(_Exp, _ExpNo))), "$(=FirstSortedValue(_DimMonthFieldName, _DimMonthNo))"),

max(aggr($(vAmount), [Month Field]))

)

And for some reason with the expression suggested solution does not work

[sunny_talwar]

Would it be possible to share a sample to test this out?

[smiling_cheetah]

Yeah, will do in a couple of mins

[smiling_cheetah]

I've created a small app which it can be tested on

[sunny_talwar]

This

=Ceil(RangeMax(

max(aggr($(=FirstSortedValue(_Exp, _ExpNo)), $(=FirstSortedValue(_DimField, _DimNo)))),

max(aggr(sum(Expression3), Dim3))

), pow(10, RangeMax(Len(RangeMax(

max(aggr($(=FirstSortedValue(_Exp, _ExpNo)), $(=FirstSortedValue(_DimField, _DimNo)))),

max(aggr(sum(Expression3), Dim3))

))-2, 1)))

gives me 140000

original value was 137941... is that not what you wanted?

[sunny_talwar]

It even works for static max

[smiling_cheetah]

Yeahh, sure it's working, Thank you, Sunny,

I just totally forgot that Initial value can have fractional part...

put floor() before rangemax and now it's working