How to Round Up in Qlikview

[brucejensen]

I'm trying to round up in Qlikview. From what I've read, I need to use the ceil function but am having trouble getting it to work correctly. Trying to round the value up 200 points from the nearest multiple of 200. Here is my example:

Value    Nearest multiple of 200     Rounded Up Value

2360               2400                                  2600

I tried =ceil(2360,200,200) but this is evaluating to 2400 instead of 2600 like I expected. Can someone help me understand how to fix this? Thanks.

[#2 · Accepted solution]

The ceil gets you to 2400, so can you not just add the extra?

=ceil(2360,200,200)+200

[brucejensen]

The value is actually an expression so it will change. I just put in a static value for illustration purposes.

[#4]

Hi Bruce,

I think what Joseph is saying should work.

You can use expression such as below:

=ceil(value,200)+200

Thanks

Sabal

[brucejensen]

I guess that will work but can someone tell me why my original example didn't work?

[#6]

It has worked, it has rounded up to the nearest 200 which in your example is 2400,

what you are actually wanting though is 2600, the round up and then the additional amount on top.

[brucejensen]

I'm confused. Didn't the offset value of 200 tell it to go 200 higher than the nearest multiple?

[#8]

Hi Bruce,

The offset value would only work as intended if it is lesser than the difference between the previous base value and the actual value or else it would simply add up in the previous base value.

For e.g:

Consider expression as: ceil(value,200,150)

Value      Nearest base value     Previous base value     Expression value

2360               2400                              2200                         2550

2350               2400                              2200                         2350

2349               2400                              2200                         2350

Hope this clears your confusion .

Thanks

Sabal

[brucejensen]

yes it does. Thank you.