When developing planning applications with SAP BW IP or BPC, you will almost certainly face the need to distribute a total value onto detail records. If this shall happen during data entry in an input-ready query you can use the disaggregation option in QueryDesigner or BW Modeling Tools – the system will take care of this task for you. If disaggregation has to be implemented as part of a (more complex) planning function however, you will need to program it yourself. And you will most likely realize, that rounding errors lead to unwanted results…
Key-figures in BW have a limited precision: for currency key-figures this is 2 decimals, for quantity key-figures it is 3 by default. There is an SAP note describing how to change the digits and decimals for key-figures, however a change in precision is limited to key-figures of type “number” (without a unit.) Floating point key-figures might seem as a quick solution, but don’t forget that type FLTP is not a decimal floating point number, but a binary one, and will thus introduce other problems.
So what is the problem with these two decimals of a currency key-figure? Imagine you have to distribute a total value of 100,00 into 6 details records equally. So you go and calculate 100,00 / 6 = 16,666666… and put that value into the two digit output of each of the six records: 16,66. But 6 * 16,66 is 99,96. Sooner or later you realize that you forgot the rounding, so your put 16,67 and have in total 100,02. In some scenarios, and depending how picky the users are, this might be acceptable – but even if it is, it makes it harder to compare and reconcile figures.
So how can you overcome this issue?
If programming the planning function in ABAP, often a very simple approach is used. While writing the figure into the detail records, you also sum them into a helper variable – this variable would contain 100,02 after your processing loop. The rounding error can be calculated as a difference, and – as probably your field-symbol used for looping anyway still points at your last detail record – you can simply add -0,02 to your last record. This solution is pretty simple, although it is not particularly elegant, because the last record takes the hit on the full rounding error. A fair algorithm would distribute the rounding error ‘randomly and evenly’ instead.
Besides this minor flaw, things start getting complicated if you do not just need to distribute a single figure onto some records, but need to consider groups of detail records each having a total value they shall receive. A single helper variable will not be sufficient anymore to track the total. And now imagine you need to do all this in SQL (in an SQLScript based PAK planning function) instead of ABAP, rendering the idea ‘add to the last record’ not so straight-forward anymore.
To see what other developers have come up with – and hoping to find a built-in rounding function which will magically solve my problem – I googled a bit, and I was surprised to find only a few answers. So I decided to roll my own solution.
And actually it turns out that the solution – as confusing it might seem at first in an SQL context with no per record manipulation – is simply common sense. Sometimes you just need to formulate it a bit differently…
Instead of the complex thinking with the two decimals, let’s multiply everything by 100. This basically means that we are not distributing 100 EUR but instead 10.000 cents. This figure divided by 6 with ‘integer division’ is 1666 and there is a remainder of 4. So, all of the six detail records get the 1666 ‘base amount’, and we have 4 additional cents to be distributed ‘randomly’. Obviously the remainder is always smaller than 6, so some records get 1 cent, the others get 0.
--Table lt_sum: g1, g2, group_value - where g1-2 are the key of a group, and group_value is the value to be distributed
--Table lt_detail: g1, g2, d1, d2 - where g1-2 identify the group, d1-d2 the detail record within the group
--Output: lt_out: g1, g2, d1, d2, distributed_value
lt_temp = SELECT detail.g1, detail.g2,
detail.d1, detail.d2,
CAST( 100 * sum.group_value AS INTEGER) AS group_value_in_cents,
COUNT(*) OVER ( PARTITION BY detail.g1, detail.g2 ) AS number_of_buckets_in_group,
ROW_NUMBER() OVER ( PARTITION BY detail.g2, detail.g2 ) AS index_of_bucket_in_group
FROM :lt_detail AS detail
INNER JOIN :lt_sum AS sum
ON sum.g1 = detail.g1
AND sum.g2 = detail.g2;
lt_out = SELECT detail.g1, detail.g2,
detail.d1, detail.d2,
CAST(
DIV(group_value_in_cents/number_of_buckets_in_group)
+
CASE
WHEN index_of_bucket_in_group <= MOD(group_value_in_cents,number_of_buckets_in_group) THEN 1
ELSE 0
END )
/ 100.0
AS DECIMAL(17,2) ) AS distributed_value
FROM :lt_temp;Note that the function DIV() I use above for the sake of an easy to understand example does not exist in HANA. Instead you can achieve integer division for example with ‘CAST(group_value_in_cents/number_of_buckets_in_group AS INTEGER)’, because this should implicitly cut off any decimals. Also the MOD() function might not exist in other SQL databases – plus it will need some additional tweaking if you need to support distribution of negative values as well. Of course the code can be further optimized – e.g. into a single statement – sacrificing readability.
For how to deal with a non-equal distribution basis, read part II.
