Round Function Not Working

[whiteymcaces]

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?

[andrey_krylov]

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

[jonathandienst]

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

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

But the others should work OK.

[marcus_sommer]

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 [

x0

0

1

"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

[whiteymcaces]

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,

[marcus_sommer]

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

[whiteymcaces]

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.

[m_woolf]

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

[marcus_sommer]

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