Manipulate %

Question

Hi - I have a sample dataset below where I have and ID field, Date field and Amount % field.

The problem I am trying to solve is that currently for each unique ID, the sum of the Amount % = 100% but I would like to reduce the number of decimal places to 2 and manipulate the last (MAX date) Amount % value such that the total for each unique ID still = 100%. Any help will be greatly appreciated!

IDDateAmount %ID_1017/1/20230.00000000000000ID_1018/1/20238.33332530600961ID_1019/1/20234.16666265300480ID_10110/1/20234.16666265300480ID_10111/1/20234.16666265300480ID_10112/1/20234.16667641413119ID_1011/1/20244.16666265300480ID_1012/1/20244.16666265300480ID_1013/1/20244.16667641413119ID_1014/1/20244.16666265300480ID_1015/1/20244.16666265300480ID_1016/1/20244.16667641413119ID_1017/1/20244.16666265300480ID_1018/1/20244.16666265300480ID_1019/1/20244.16667641413119ID_10110/1/20244.16666265300480ID_10111/1/20244.16666265300480ID_10112/1/20244.16667641413119ID_1011/1/20254.16666265300480ID_1012/1/20254.16666265300480ID_1013/1/20254.16667641413119ID_1014/1/20254.16666265300480ID_1015/1/20254.16666265300480ID_1016/1/20254.16667641413119ID_10210/1/20234.16666014492867ID_10211/1/20234.16666014492867ID_10212/1/20234.16667971014266ID_1021/1/20244.16666014492867ID_1022/1/20244.16666014492867ID_1023/1/20244.16667971014266ID_1024/1/20244.16666014492867ID_1025/1/20244.16666014492867ID_1026/1/20244.16667971014266ID_1027/1/20244.16666014492867ID_1028/1/20244.16666014492867ID_1029/1/20244.16667971014266ID_10210/1/20244.16666014492867ID_10211/1/20244.16666014492867ID_10212/1/20244.16667971014266ID_1021/1/20254.16666014492867ID_1022/1/20254.16666014492867ID_1023/1/20254.16667971014266ID_1024/1/20254.16666014492867ID_1025/1/20254.16666014492867ID_1026/1/20254.16667971014266ID_1027/1/20254.16666014492867ID_1028/1/20254.16666014492867ID_1029/1/20254.16667971014266

Thank you in advance!!!

binu_acs · Answer

@jaiiracha

Manipulate%_220124.yxmd

jaiiracha · Answer

You're exactly right, and to avoid that, what I am trying to achieve is that I would like to round everything to 2 decimal places but then manipulate the last (MAX date) Amount % value such that the total for each unique ID still = 100%.

So for ID_101, the last Amount % (Date = 6/1/2025) = 4.10 after rounding all preceding values to 2 decimal places (see table below).

IDDateAmount %ID_1017/1/20230.00ID_1018/1/20238.33ID_1019/1/20234.17ID_10110/1/20234.17ID_10111/1/20234.17ID_10112/1/20234.17ID_1011/1/20244.17ID_1012/1/20244.17ID_1013/1/20244.17ID_1014/1/20244.17ID_1015/1/20244.17ID_1016/1/20244.17ID_1017/1/20244.17ID_1018/1/20244.17ID_1019/1/20244.17ID_10110/1/20244.17ID_10111/1/20244.17ID_10112/1/20244.17ID_1011/1/20254.17ID_1012/1/20254.17ID_1013/1/20254.17ID_1014/1/20254.17ID_1015/1/20254.17ID_1016/1/20254.10

Hope that makes sense. Appreciate the help!

Raj · Answer

@jaiiracha IF you round it till 2 decimal places this is not going to work and we should not round the final this without complete information
as it is possible that you miss some transaction with some small value and this can cause error

without rounding this is perfect -100

after forced 2 places this become -100.08 and 100.07 and we should not put imaginary condition in this case.