Format number via script

[pkelly]

I have a cost per tonne field which I need formatted to 6 dp via my script.

I have tried...

Num(((tpl_Fixed + (tpl_Variable * tmp_Weight)) / tmp_Weight),'0.000000') AS tpl_Cost_PerTonne

With no success....

Where am i going wrong...?

NB - I know that I can format via my table but I append this figure to other tables and it appends as 6 d.p. - I need to get it to 6 d.p. in the main script to allow me to square other calculations properly.

Regards

Paul

[#2 · Accepted solution]

I think you need to use Round().

Round((tpl_Fixed + (tpl_Variable * tmp_Weight)) / tmp_Weight), 0.000001)

It doesn't appear that the number formats work the same in the script as they do in tables. In a table, #,##0.000000, would get you the proper number, but it doesn't seem to work in the script.

[johnw]

Off topic a bit, but I wouldn't calculate "something per something else" in the script. I would only have "something" in the script and "something else" in the script. That way, you can do any sort of aggregation you want. If it has already been calculated in the script, the only aggregation you can do is the one in the script, because you've lost the details.

For example, your cost per ton might be by customer. If you do that in the script, and don't store the cost and the tons separately, you can't calculate an overall cost per ton, or a cost per ton by the country the customer is based in, or whatever.

If you need it to 6 decimal places to square with other calculations, I'd just take that as a charting challenge, and it will probably involve something like aggr(round(your cost per ton claculation,.000001),the key to the table in the script that you wanted to do this calculation on). Or I would take it as a user acceptance challenge, and teach them that it's better to not introduce rounding errors into their data, even if as a result it may LOOK like data doesn't add up.

And to explain where you were going wrong, the num() function is only applying a display format to your number. Internally, it was still tracking your number to greater precision than 6 decimal places.