Disaggregating an "Adjusted" key figure KF2 proportionally by key figure KF1 - new combin. of KF1

[vincentverheyen]

Let's imagine a stored key figure KF2 which is maintained in a Planning View at Month & Product level.
Now, this KF2 is an Adjusted key figure. It's goal is to adjust (over-rule) another key figure KF1.

• Step 1)

The formula for the final KF3, could be something like this:

KF3 = KF1, unless KF2 is not empty, then KF3 = KF2

In an actual formula, it would probably be something like this:

KF3@MTHPRODCUST = JF("KF2@MTHPRODCUST">=0, "KF2@MTHPRODCUST","KF1@MTHPRODCUST")

Or in an alternative flavour, something similar with IF(ISNULL(... as such:

KF3@MTHPRODCUST = IF(ISNULL("KF2@MTHPRODCUST"),"KF1@MTHPRODCUST","KF2@MTHPRODCUST")

Now, important to note is that this KF2 has proportionate disaggregation according to another key figure (the setting here is disaggregation according to "Other Key Figure – Stored Values"), namely according to key figure KF1, which is a Stored Key Figure at Base Planning Level Month & Product & Customer. Hence, IBP technically requires us for KF1 to have the same Base Planning Level as KF2, even though KF1 is only manually maintained on a higher level in another Planning View (Month & Product). The disaggregation works well.

• Result of Step 1)

For example, let's say we had a combination of Month & Product1 & Customer1 already in KF1, before we had entered the KF2's values in Step 1. That menas, the disaggregation is working, the KF2's values of 20pieces at Month & Product1 level are trickling down correctly according to the KF1, to arrive at KF2's value of 20pieces at Month & Product1 & Customer1 level (we are assuming for the moment, there is only 1 Month & Product & Customer combination for Product1, so the 20 pieces are fully disaggregated onto that 1 combination Month & Product1 & Customer1).

• Step 2)

The only problem is that, once you create a new Month & Product1 & Customer2 combination (so in our example: we extend it to include a new Customer2), which impacts KF1, the adjusted KF2 will remain empty in this new combination, and thus the KF3 will not take the value of KF2 (which we had previously maintained at Month & Product1 in the Planning View in Step 1).

Note that, problematically, KF2 has not trickled down to the Month & Product1 & Customer2 level. The reason is that we had maintained KF2 in Step 1, which is before we created the extra Customer2 combination for KF1 in Step 2.

On a Month & Product view, our KF2 now still looks the same as in Step 1, however KF3 is too high (120pcs exactly too high) in May and in June.

----------------------------------------------------------------------------------

How to solve this conundrum?

• Solution 1)

I think one way, is to create a scheduled job with a copy operator from KF2 into a new KF2B. And to let KF3 depend on KF2B, not KF2. Or even easier: use a copy operator to copy KF2 into itself, on Product level. In either case, the copy operator should be on level Product.

• Solution 2)

If not, one needs to re-enter/slightly change manually the existing value in KF2 on Month & Product level, which will re-activate the trickling down via the disaggregation. See here an example, where we change the value of KF2 (from 20pcs to 22pcs) in May, to illustrate the effect:

Notice that KF2 trickles down correctly now, and thus also changing the value of KF3 as desired.

KF2 trickles down (disaggregates) correctly now, since we executed the change (from 20pcs to 22pcs) after Step 2, so after the new Month & Product1 & Customer2 combination was created.

This solution is not practical, it requires manual intervention.

• Idea for Solution 3)

I had also considered to perhaps use a "Delta Adjustments" key figure, instead of an absolute value "Adjusted" key figure? This way, KF3 can be a substraction calculate key figure. However, the same problem remained. It did not solve the problem.

• Idea for Solution 4)

Can it perhaps be solved by using KF1 as Additional Input? However, KF2 is a stored KF, so at the Request level, one can not have an Additional Input since this is an Aggregation? So, one needs to invoke a second Helper KF perhaps? Could this work to re-activate the trickling down of KF2 which is maintained at Month & Product1 level into the new combination at KF1 Month & Product1 & Customer2 level?

----------------------------------------------------------------------------------

But perhaps you have other solutions?