topic Re: Round Function Not Working in QlikView

Round Function Not Working

whiteymcaces — Fri, 24 Aug 2018 04:46:58 GMT

I have raw data that I am loading from a QVD file. The QVD is created from a Select * From statement.

I noticed that zero lines were appearing in my charts, even thought I was surpressing zero's etc.

Upon investigation, I found that the number data in the QVD had up to 13 decimal places, so even though I was only displaying 2 decimal places, the value was not zero, like 0.0008 or 0.0000015 etc.

I used the Round function in the script to clean all this up, however, the resulting values are identical to the source values, so Round(0.00015, 0.01) = 0.00015 not 0.00.

Here are some examples of my script :

Round(Amount, 0.01) as Amount,

Round(Amount * 10, 0) / 10 as Amount,

Round(Floor(If(IsDebit = 1, Amount * -1000, Amount * 1000)) / 1000, 0.01) as Amount,

Amount as TestAmount,

Data in TestAmount and Amount always match.

The Round function has no effect.

Any ideas?

Re: Round Function Not Working

andrey_krylov — Fri, 24 Aug 2018 06:03:37 GMT

Very strange. This load script

[Table]: LOAD *, Round([Amount 1], 0.001) as [Amount 2]; LOAD RAND()*100 as [Amount 1] AutoGenerate 10;

gives me

Re: Round Function Not Working

jonathandienst — Fri, 24 Aug 2018 07:27:37 GMT

This one won't work (can't round to 0)

=Round(Amount * 10, 0) / 10 as Amount,

But the others should work OK.

Re: Round Function Not Working

marcus_sommer — Fri, 24 Aug 2018 07:30:02 GMT

I would expect that round() worked like intended at least with the following inline-snippet it does and by loading from a qvd it should be the same:

t: load x0, num(x0) as x1, round(x0, 0.01) as x2 inline [

"0,15"

"0,015"

"0,0015"

"0,00015"

];

left join(t)

load x0, num(fieldvalue('x2', recno())) as x3 resident t;

results in:

whereby x2 is set to display multiple digits within the tab numbers. The join of the rounded fieldvalues from x2 as x3 is to show how the values are internally stored so that no formatting could mislead the interpretation. The NULL's here happens because x2 has only 4 fieldvalues and recno() within the join-load goes up to 6 which meant that the last both records from x2 have not a new value which could be stored else they contain an already stored value which is in this case 0.

I hope its helpful for you to find the reason to your issue.

- Marcus

Re: Round Function Not Working

whiteymcaces — Fri, 24 Aug 2018 13:24:27 GMT

Here are my results:

Amount as TestAmount,

If(IsDebit = 1, Amount * -1, Amount) as Amount,

Left(Text(Amount), Index(Text(Amount), '.') + 3) as Amount2,

Text(Amount) as Amount3,

Round(Num#(Evaluate(Text(Amount))), 0.01) as Amount4,

Re: Round Function Not Working

marcus_sommer — Fri, 24 Aug 2018 14:10:08 GMT

By numbers different to 0 it's a different case. Important is to differ between the real (stored) values and those ones which are displayed which are not necessary the same. The reason is the used number-system of Qlik which is a binary system and not a decimal system and in which not each number has an exact value. This is quite well explained here: Rounding Errors.

- Marcus

Re: Round Function Not Working

whiteymcaces — Fri, 24 Aug 2018 15:07:52 GMT

I am trying to create a Table that shows the Outstanding Balance for clients here is an example of the issue, where the outstanding balance is zero, however, it is appearing in the chart.

Re: Round Function Not Working

m_woolf — Fri, 24 Aug 2018 15:28:10 GMT

Goto Chart|Properties|Number and set the desired formatting for each field.

Re: Round Function Not Working

marcus_sommer — Fri, 24 Aug 2018 15:49:37 GMT

If I understand your screensgot right then has it nothing to do how the data are stored or loaded else how they are calculated in the tables and you need to apply the rounding there maybe with something:

round(sum(Outstanding), 0.01)

- Marcus