Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

smiling_cheetah
New Contributor III

Rounding a number

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:

InitialAfter rounding
1234513000
123130
998710000
510

Would appreciate any help

Thanks

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: Rounding a number

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

];

Capture.PNG

10 Replies
MVP
MVP

Re: Rounding a number

Try this

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

MVP
MVP

Re: Rounding a number

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

];

Capture.PNG

smiling_cheetah
New Contributor III

Re: Rounding a number

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

MVP
MVP

Re: Rounding a number

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

smiling_cheetah
New Contributor III

Re: Rounding a number

Yeah, will do in a couple of mins

smiling_cheetah
New Contributor III

Re: Rounding a number

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

MVP
MVP

Re: Rounding a number

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?

Capture.PNG

MVP
MVP

Re: Rounding a number

It even works for static max

Capture.PNG

smiling_cheetah
New Contributor III

Re: Rounding a number

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