Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
smiling_cheetah
Creator
Creator

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

10 Replies
sunny_talwar

Try this

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

sunny_talwar

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
Creator
Creator
Author

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
Creator
Creator
Author

Yeah, will do in a couple of mins

smiling_cheetah
Creator
Creator
Author

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?

Capture.PNG

sunny_talwar

It even works for static max

Capture.PNG

smiling_cheetah
Creator
Creator
Author

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