topic Re: Rounding to 3 Significant Figures in QlikView

Rounding to 3 Significant Figures

michelle_le — Wed, 01 Feb 2017 07:38:56 GMT

Hi Team

I am looking for some guidance on how to round numbers to 3 Significant Figures?

We are calculating Averages of chemical readings, and using a rounding to 3 decimal places is not ideal for this particular use case.

Thanks in advance!
Michelle

Re: Rounding to 3 Significant Figures

Anil_Babu_Samineni — Wed, 01 Feb 2017 09:48:07 GMT

You may describe more with screen shots.

As i understand, you need something like after decimal you want to print 3 number format or what?

Re: Rounding to 3 Significant Figures

jonathandienst — Wed, 01 Feb 2017 09:51:34 GMT

3 Decimal places:

Round off

=Round(Value, 0.001)

Round up

=Ceil(Value, 0.001)

Round down

=Floor(Value, 0.001)

3 significant digits:

=Round(Value, Pow(10, Floor(Log10(Value) - 2)))

Re: Rounding to 3 Significant Figures

michelle_le — Thu, 02 Feb 2017 01:12:31 GMT

Thanks Anil and Jonathan for your swift response.

Here is an example of what I am trying to achieve:

Chemical	Value	Round(Value, 0.001)	Round Value to 3 Sig Figures
apples	742,396	742,396.000	742000
oranges	0.07284	0.073	0.728
bananas	231.45	231.450	231

You'll note that depending on the value, we only want to round to 3 significant figures, not 3 decimal places.

Some further context to Sig.Figs. as described here: Rounding and Significant Digits | Purplemath

Thanks

Michelle

Re: Rounding to 3 Significant Figures

michelle_le — Thu, 02 Feb 2017 05:56:41 GMT

Further to this, our requirements are to only apply this to numbers less than one, i.e.:

Between 0-1.......Round to 3 significant figures.
Greater than 1....Round(Value, '0.001')

Based on this, I have come up with the following method and currently testing. Let me know if someone comes up with a smarter way

If(Avg(VALUE) >=1, Round(Avg(VALUE), '0.001'),
Round(Avg(VALUE), 1/(Pow(10,3+
SubstringCount(TextBetween(Avg(VALUE),'.',Left(Num(TextBetween(Avg(VALUE), '.', Null())),1)),'0')
))))

Re: Rounding to 3 Significant Figures

Chanty4u — Fri, 03 Feb 2017 07:23:22 GMT

Hi,

Did you tried this?

=ROUND(1234567,-6) = 1000000 // 1 sig. digit
=ROUND(1234567,-5) = 1200000 // 2 sig. digits
=ROUND(1234567,-4) = 1230000 // 3 sig. digits
=ROUND(1234567,-3) = 1235000 // 4 sig. digits

Re: Rounding to 3 Significant Figures

michelle_le — Fri, 03 Feb 2017 07:32:47 GMT

Thanks chanty4u‌, but I don't think the QlikView Round() function behaves that way?

Re: Rounding to 3 Significant Figures

Chanty4u — Fri, 03 Feb 2017 07:40:20 GMT

Even I haven't checked . Is issue resolved ?

Re: Rounding to 3 Significant Figures

michelle_le — Fri, 03 Feb 2017 08:10:26 GMT

No worries.

Yes I have resolved using my workaround code above. But happy to take on any suggestions for improvement.

Re: Rounding to 3 Significant Figures

Chanty4u — Fri, 03 Feb 2017 09:36:39 GMT

good to hear cool.