I have used it for years, but rarely thought about its power until recently.

At a simple level, you can call the function like this:

**Round(3.14159)**

Which will round the value to the nearest integer, thus giving me a nicely rounded 3.

To round to a significant number of places, instead of telling it the number of places that I want (as you do in Excel), I need to give it a step value to round to. So, if I want to round to the nearest integer, the step value is 1. Round to 3 places, the step value is 0.001:

**Round(3.14159, 0.001)**

Which gives me 3.142 - the value is rounded to the nearest 0.001.

This is pretty much the way that I have always used the round function. But you are not mandated to have only 1s in your step value! How about this:

**Round(3.14159, 2)**

This would yield a value of 4 - pi rounded to the nearest 2.

How about this:

**Time(Round(Time#(Times, 'HH:mm'), (1/(24*4))))**

Here I a parsing a text field called "Times" that has a time value in "HH:mm" format. I then round it to the nearest quarter hour. Would you find that useful for log file analysis?

There is also a third parameter that we can give to Round - an offset. So, for example, if I want to generate some random numbers:

**Load**

**Round(Rand()*100, 1) as RandVal**

**AutoGenerate(1000000);**

This would generate a range of numbers between 0 and 100. If I look at the spread of those numbers I can see that 0 and 100 have only half of the occurrences of all the other numbers:

This happens, of course, because the range of values that will produce either 0 (0-0.4999) or 100 (99.5-99.9999) are half of the range of values that would produce and other number (e.g. 5 = 4.5-5.4999).

So I will offset each of the values by 0.5 and round those. This will give me the result I need.

**Load**

**Round(Round(Rand()*100, 1, 0.5)) as RandVal**

**AutoGenerate(1000000);**

Simplistic example, but interesting.

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner

Follow me on Twitter: @stephencredmond

This comment has been removed by a blog administrator.

ReplyDeleteThis comment has been removed by a blog administrator.

ReplyDeleteThis comment has been removed by a blog administrator.

ReplyDeleteThis comment has been removed by a blog administrator.

ReplyDelete