How do I transfer values between key figures without causing rounding issues?

[ptopolewski]

Dear SAC Community,

We want to transfer values from Key Figure A with 7 decimal places to Key Figure B with 2 decimal places. Copying the data between these key figures leads to rounding errors. The data needs to be copied to Key Figure with 2 dec. places as we have to export the data to table ACDOCP in our S4 System in which the key figures are restricted to two dec. places. How can we transfer the values from key figure A to keyfigure B without getting rounding errors?

In addition, I will give you a more detailed example. In our scenario we want to distribute 5.000 equally with a 50% share to two Order numbers. In our planning story, we enter 5.000 on year 2026 and the value gets automatically allocated to the underlying periods. Here we use Key Figure A with 7 decimal places

Order	Sum	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
#	5000	416,6666667	416,66666676667	416,6666667	416,6666667	416,6666667	416,6666667	416,6666667	416,6666667	416,6666667	416,6666667	416,6666667	416,6666667

Now we perform the allocation with a 50% per Order nr. The 7 decimals come in handy as we won't face any rounding issues when performing the allocation.

Order	Sum	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Order1	2500	208,315	208,335	208,335	208,335	208,335	208,335	208,335	208,335	208,335	208,335	208,335	208,335
Order2	2500	208,315	208,335	208,335	208,335	208,335	208,335	208,335	208,335	208,335	208,335	208,335	208,335

In order to prepare the data for the S4 ACDOCP export, we move the values from key figure A to key figure B via a data action, which results in rounding errors.

Order	Sum	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Order1	2.499,94	208,31	208,33	208,33	208,33	208,33	208,33	208,33	208,33	208,33	208,33	208,33	208,33
Order2	2.499,94	208,31	208,33	208,33	208,33	208,33	208,33	208,33	208,33	208,33	208,33	208,33	208,33

Best regards

Pascal

[spurwar]

Try creating the below calculated measures or write a data action to convert the numbers from 7 decimal places to 2 decimal places. 

Below is a rough way to convert the data using calculated measures and can be optimised further.

Measure ID	Measure Desc	Formula
Amount	Original Amount(7 decimal places)
Amount_Round	Amount_Round	FLOOR([Amount]*100 )/100
Amount_Residue	Amount_Residue	( [Amount]-[Amount_Round])
Month	Derive Month from Month desciption	IF([d/Period].[p/MONTHDESC] = "Jan", 1, IF([d/Period].[p/MONTHDESC] = "Feb", 2, IF([d/Period].[p/MONTHDESC] = "Mar", 3, IF([d/Period].[p/MONTHDESC] = "Apr", 4, IF([d/Period].[p/MONTHDESC] = "May", 5, IF([d/Period].[p/MONTHDESC] = "Jun", 6, IF([d/Period].[p/MONTHDESC] = "Jul", 7, IF([d/Period].[p/MONTHDESC] = "Aug", 8, IF([d/Period].[p/MONTHDESC] = "Sep", 9, IF([d/Period].[p/MONTHDESC] = "Oct", 10, IF([d/Period].[p/MONTHDESC] = "Nov", 11, IF([d/Period].[p/MONTHDESC] = "Dec", 12, 0 ))))))))))))
Amount_adj	Adjustmnet to be posted	IF([Month] <= ROUND(SUBTOTAL([Amount_Residue],[d/Period])/0.01,0), 0.01, 0)
Amount_S4	Amount_S4 ( 2 decimal places)	[Amount_Round]+[Amount_adj]

Example:

	Total	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Amount	5000	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6666	416.6666	416.6666	416.6666
Amount_Round	4999.92	416.66	416.66	416.66	416.66	416.66	416.66	416.66	416.66	416.66	416.66	416.66	416.66
Amount_Residue	0.08	0.0067002	0.0067002	0.0067002	0.0067002	0.0066999	0.0066999	0.0066999	0.0066999	0.0065999	0.0065999	0.0065999	0.0065999
Month		1	2	3	4	5	6	7	8	9	10	11	12
Amount_adj		0.01	0.01	0.01	0.01	0.01	0.01	0.01	0.01	0	0	0	0
Amount_S4	5000	416.67	416.67	416.67	416.67	416.67	416.67	416.67	416.67	416.66	416.66	416.66	416.66

[ptopolewski]

please delete this comment

[ptopolewski]

please delete this comment

[ptopolewski]

please delete this comment

[ptopolewski]

Hi spurwar, thanks for your reply and ideas! I have checked it but unfortunately it is not working for me. The subtotal for the residiual amount is not determined correctly. My Amount_Residue is 0.005 for all periods and when i try to get the subtotal for Amount_Residue it shows me 0.00. The subtotal function works fine with other key figures. Could it be that the subtotal function does not work with small numbers like 0.005? Best regards Pascal