No fixed decimal data type for currency? What are the best practices?

How are people working around the lack of a fixed decimal currency friendly data type? I find myself unable to show and maintain a universal 2 fixed decimal places for my currency data throughout a workflow. All of the posts that I have found only seem to be recommending that users convert numbers to a string before a final presentation, but I am concerned with the display and review during financial calculations as numbers within KNIME.

How do I add the empty decimal places behind round currency numbers? Should I really be using constant multi column re-rounding formulas to return calculations to two decimal places after nodes like GroupBy?

Hi @iCFO , it is indeed more of a display thing. If you think about it, 1 = 1.0 = 1.00 = 1.000 = 1.000 = 01.00, so the real value is really just 1. Likewise, 1.50 = 1.5. That is why it is what you see in a calculator.

You can use the function toFixed(2) via the Column Expression.

For example: column("A").toFixed(2) would round the values of Column A to 2 decimal places. The result will display in 2 decimal places only if the column containing the results is of type String. Even type Decimal will not, as you can’t specify the precision of the decimal.

In terms of good practice, because you lose precision with rounding off, it’s better to round off at the end, rather at multiple times/places. That being said, it’s also about defining what “at the end” mean. In terms of accounting, this can be daily, weekly, monthly, quarterly, yearly, etc, basically the end of a term.

In accounting, calculations are done at the end of a term, and that’s when the numbers are rounded, and then you kinda reset/start over for the next term, or carry forward some numbers over to the next term.

1 Like

Thanks for the quick response @bruno29a. I was afraid that was going to be the answer… Unfortunately, these are the same workarounds that I have been using and they cause a waste of time, unnecessary workflow clutter and an unfriendly presentation / user experience. I suppose if a user were just doing a few simple calculations, all parties had 100% faith in the calculation engine and user workflow design, the approach and processes used required no explanation, and only the final output would ever be presented or viewed, then the “.toFixed(2) / convert to string” approach at the end might be adequate. Unfortunately those conditions are extremely rare in my world for custom projects. The node by node presentation within the workflow is often more important than the output.

I need to be able to do live walkthroughs and be able to review each individual node configuration / output tables on the fly with Accountants, CPAs, Comptrollers, CFOs, Auditors, Managers, Owners, etc. This requires a clean consistent currency presentation thought the workflow.

I have presented 2 separate forensic accounting projects in the last 2 days via interactive video conference. Both projects included several requests for a more friendly right aligned fixed decimal accounting presentation, and both projects became nervous and requested “excel calculation proofs” the second they viewed the higher precision output of a GroupBy Node prior to rounding. These were not issues when doing walkthroughs using a fixed decimal / currency friendly data type on Alteryx projects. It added hours of work to the project for me to create several multi sheet excel calculation proofs / manual excel formatting.

Forensic accounting projects like these often generate a single adjustment Journal Entry with a few support data sheets as the output which receive little scrutiny. The importance of a user friendly presentation is actually more important when presenting the calculation process within the workflow itself, then in the simple cleaned up output.

I really hope that a currency friendly fixed decimal data type is added in the future. I posted a new feature suggestion. Please add your support if you find yourself in need of a fixed decimal number or more currency friendly data type.

@iCFO I asked for a +1 in the request

1 Like

if you right click on the output table column there are some render options but they are limited.

1 Like

None of the rendering options offer even a temporary fixed 2 decimal presentation, but I don’t find the individual table output adjustments like Rendering Options of much use anyway since the settings do not persist. Every adjustment to any output table is lost upon the next execution of the node.

The challenge when working with currencies is less the display with two decimals.
Also the mathematics is not the standard we are used to.
In our normal world we know that

(a * t) + (b * t) = (a+b)*t

When we talk about currencies with two decimals and the standard rounding method this math will give (slightly) different results. Yes it’s only a failure of 0,01. But if you need to have correct calculations in accounting: That’s wrong.

For calculation with currencies you need both a currect display (with internal rounding) and the correct mathematic rules.

BTW: Some time ago there was a dedicated programming language especially for commercial software. It is called COBOL - Wikipedia and still in use for dedicated applications.

2 Likes

Yup, indeed COBOL is still used @knimediger cause it’s reliable. It brings up some memories :smiley:

Along the lines of the comments that @knimediger made above:

With the current numeric data options in KNIME, I have been considering it “best practices” to immediately round calculations that yield a higher precision than 2 decimal points back to the fixed 2 decimals. It seems to me that allowing greater precision currency numbers to continue downstream as a basis for further calculations could compound into a proofing failure if all rounding was held until the end. That being said, most of my work in KNIME occurs at the highest granularity of financial data. I just need to ensure that Tableau, Excel, KNIME & Calculators will all generate the same currency calculations at the reporting stage when cross referencing.

That’s a personal choice, and may be it works for you. As a mathematician, I don’t agree with this. I will always round off at the end.

Consider this: With the marketing strategy where most articles are sold at a sale price of x.98 or x.99, when you add the taxes, you do the calculations based on the total instead on the individual items.

That is why when you buy 7 of the same items at once, the amount you will pay will be slightly different from the total of going 7 times and buying 1 item at a time. And the more items you buy individually vs at the same time, the greater that difference will be.

As we know, the more decimal places we have, the more precise we get. 2 decimal places is not very much precise at all, but in terms of monetary value, it is what’s required. All more reason why to avoid rounding off often or as early, at least mathematically.

In the end, in terms of accounting, it depends on which side of the fence you are. In some cases it’s more profitable to round off early, and in other cases it’s more profitable (or less costly) to round off as late as possible.

That’s what I mentioned above. Currency mathematics on the set of numbers with two digits is not distributive.

It’s interesting to hear that mathematics is defined by results of software like EXCEL.
I would expect that EXCEL returns the same results as mathematical models.

I am speaking of further calculations which are based off of reporting sums at various levels of detail being calculated on each platform. Budget manipulation calculations and forecasts need to yield the same results on all platforms, so I want the same currency precision used as a basis for those calculations on all platforms. Sorry if the wording is a bit convoluted…

@iCFO Yes, I understand you. You tried to reduce the workload and now you’re stuck with more work than before, because xkcd: Standards. I can see why it’s next to impossible to explain numeric representation to accounts. I work in the railway sector, where wheel forces are measured in tons. I once tried to explain why that’s wrong and that it’s not the same thing as Newton, but quickly gave up.
@knimediger Excel has 16 digits precision, so mathematically speaking, nothing is on fire there. It’s widely in use for business economy applications and the folks defending it seem to not like change.

My situation is probably further complicated because Tableau is my primary source of truth and currency is running off a fixed decimal data type. It is the main source for calculations / budgeting / reporting / forecasts / valuations / etc. Static reporting is also done in KNIME for key areas which should match Tableau. All reports should at the same level of precision that the user sees for proofing and user confidence.

That being said, I do use highly precise “rate” variables for calculations rather than simple currency formulas in many areas.

I just had the idea that maybe using integers would help (cents instead of euro, dollar, …) but many nodes, such as the groupBy, will output mean values etc. as decimal regardless. And it’s probably too exotic for your colleagues.
The only well designed solution would probably be a separate financial data type. New renderers won’t cut it, even if they could be set to default. Rounding after every node is ridiculous. Casting to string only works for the final result. But aggregation nodes would still have to be taught to have user setable data types anyway.

I hope your weekend is going better than this thread :sweat_smile:

Thanks for the idea @Thyme. Integer loses too much context, would trigger some serious concerns in an outside review, and (as you said) I would still be rounding after some nodes…

I was really hoping that there was some JavaScript trick or preference adjustment that I hadn’t heard of that could force a numeric data type to hold a fixed decimal.

My current approach is to have a macro shortcut that will copy and place a pre-configured node with the rounding formula assigned to all of the currency columns at the cursor position and then connect it to the node on the left. Far from ideal, but at least I don’t waste as much time.

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.