cancel
Showing results for 
Search instead for 
Did you mean: 

IBP data population from PROD to LOCPRODCUST level

peter_casper
Contributor

Hi community,

I got stuck in populating data from PRODWEEKLYCURR level to LOCPRODCUSTCURRWEEKLY level and I am not sure how to get there.

What I have done?

I populated a price attribute from product MDT into the planning area following the attribute as key figure approach. The price gets properly calculated for the base planning level "PRODWEEKLYCURR".

Now I want to calculate revenue key figures at LOCPRODCUSTCURRWEEKLY level by multiplying this price with the respective QTY of the key figures at LOCPRODCUSTWEEKLY level, similar like it's done for CONSENSUSDEMANDREV.

However the system complains about more than 2 different planning levels if I replace "SALESFCSTPRICE@LOCPRODCUSTCURRWEEKLY" with the key figure "IBPPRDUNITPRICE2@PRODWEEKLYCURR".

How do I lift my current price to LOCPRODCUSTCURRWEEKLY level?

regards, Peter

piyush_parekh
Active Contributor
0 Kudos

Hi Peter,

Is there a possibility that you have another stored currency key figure at WKPRODLOCCUSTCURR level (say, actuals rev or sales forecast rev) and planning combinations for that key figure will be same as that of Consensus Demand Rev key figure? For e.g. if that key figure has only 1 combination as P1, L1, C1, USD ; Consensus Demand Rev will also have the same combination?

Regards,

Piyush

peter_casper
Contributor
0 Kudos

Yes, there are a couple of other revenue related key figures at LOCPRODCUSTCURRWEEKLY level which I defined. Similar like CONSENSUSDEMANDREV.

How would this help?

regards Peter

Accepted Solutions (1)

Accepted Solutions (1)

peter_casper
Contributor

Hi all, Hi Piyush,

this topic kept me busy but I managed to get it working properly now after starting from scratch. A couple of issues were related to the fact that changing calculations based on an already activated one seems to make trouble in some of the cases. I have the feeling that some calculation artefacts are remaining at DB level which do not get updated properly. However this was just one bit which made the successful application a bit harder. Nevertheless I would like to make a short summary how I managed to get it working, especially based on your idea w/ the dummy key figure.

1. I created a dummy key figure at LOCPRODCUSTWEEK level (w/o CURR) due to the fact that all relevant qty related key figures which are the basis for revenue calcualtion are at that level. Thereby I do not need to worry that all key figure cells are initialized properly and I achieve a 100% synchronized price population for all kf cells where I do have quantities. (KF = Calculated)

2. Creation of an artifical price calculation at LOCPRODCUSTCURRWEEK which gets calculated based on dummy key figure@LOCPRODCUSTWEEK and price@PRODWEEK which additionally gets multiplied by EXCHANGERATE@CURRFRTOWEEKLY. (KF = Stored)

3. I incorporated the calc price into (from 2) into the revenue related kf by simply multiplying it. All revenue related kf are also calculated only based on calculated price calc kf. The calculation logic looks like this:

"REV@LOCPRODCUSTCURRCURRTOWEEKLY" = "CALPRICE@LOCPRODCUSTWEEKLY" * "REQQTY@LOCPRODCUSTWEEKLY" * "EXCHANGERATE@CURRFRTOWEEKLY"

piyush.parekh: Special thanks to you for your continuous help. Your dummy key figure proposal was the missing puzzle piece which helped to lift the static product price on product | location | customer level. Wouldn't have worked w/o it.

Potentially the one or the other can make use of this approach as well.

Cheers, Peter

Answers (1)

Answers (1)

piyush_parekh
Active Contributor

Hi Peter,

Below suggestion will work in cases where there is a stored key figures defined and data is stored at LOCPRODCUSTCURRWEEKLY level. Only for those planning combinations, system will be able to convert price key figure at PRODWEEKLY to LOCPRODCUSTCURRWEEKLY.

1. Create a dummy stored key figure at LOCPRODCUSTCURRWEEKLY level which will always carry value as 1 by writing below calculation.

Dummy KF @LOCPRODCUSTCURRWEEKLY = IF(0=1, Dummy KF @LOCPRODCUSTCURRWEEKLY, 1)

Mark Dummy KF as stored in above calculation. It will put a calculated value as 1 in this key figure at LOCPRODCUSTCURRWEEKLY level (only for those planning combinations which are created by other stored key figures at LOCPRODCUSTCURRWEEKLY level).

2. Create a calculated "price cal" key figure at LOCPRODCUSTCURRWEEKLY level and write below calculation.

Price cal @LOCPRODCUSTCURRWEEKLY = Dummy KF @LOCPRODCUSTCURRWEEKLY x Price @PRODWEEKLY

Mark Dummy KF as calculated and Price key figure as stored. You can use "price cal" key figure in revenue calculation and can add currency conversions as well.

Regards,

Piyush

peter_casper
Contributor
0 Kudos

Hi Piyush,

I tried to follow your approach but while applying step 2, the system complains about the planning levels.

"The union of attributes of all input planning levels should be equal to all attributes of output planning level."

But I can't find no attributes in PRODWEEKLY which are not yet part of LOCPRODCUSTCURRWEEKLY based on the following calculation:

IBPPRDUNITPRICECAL2@LOCPRODCUSTCURRWEEKLY = "H10CONSTVAL2@LOCPRODCUSTCURRWEEKLY" * "IBPPRDUNITPRICE@PRODWEEKLY"

H10CONSTVAL2 is the dummy key figure on LOCPRODCUSCURRWEEKLY level.

regards Peter

piyush_parekh
Active Contributor
0 Kudos

Hi Peter,

Please check if root and non-root attributes added from PROD MDT in both planning levels are same. Also verify if root time level attributes and non-root time level are same in both planning levels (including weight factors).

Regards,

Piyush

peter_casper
Contributor
0 Kudos

Hi Piyush,

actually this was the case already. However I started from scratch and made it work now. If have the dummy key figure w/ constant value of '1' in place and a calculated price value at LOCPRODCUSTCURRWEEKLY level which properly calculates the value for each LOCPRODCUSTCURR combination. This calculated key figure has been marked as "stored".

I even incorporated this calculated price kf into the revenue calculation which also worked fine but I the key figure remains empty. If i take a look at the master data (planning objects @ Location | Product | Customer | Currency | Weekly) the respective calculated price key figure does not contain values which is most probably the reason why the revenue kf stays empty. 😞

Could you imagine a reason for that behaviour?

regards, Peter

piyush_parekh
Active Contributor
0 Kudos

Hi Peter,

As a pre-requisite for this approach to work, there should a combination available in the DB at Location | Product | Customer | Currency | Weekly already. Only for these combinations, system will populate "calculated" values of '1' in dummy key figure. If you pull this dummy key figure in Master Data Workbook, you will not find any values populated as there is no "stored" value in this key figure. However, you should find all the available combination at Location | Product | Customer | Currency | Weekly level. Make sure you've marked this dummy key figure as "calculated" in calculation validation and not "stored" when you multiply it with Price key figure so that system multiplies 1 which price values instead of stored values "Null" which probably is the reason revenue key figure is empty. Also, maintain Exchange Rate values for respective time periods if you've added currency conversion.

All the best!

Regards,
Piyush

peter_casper
Contributor
0 Kudos

Hi Piyush,

I went once more through all the settings proposed by you incl. master data check. Everything was already set according to your explanation. I also played around by marking the different kf as stored and calculated at the same time. Nothing worked out.

Here's the current result I do get:

The calculated dummy key figure H10CONSTVAL1 gets properly displayed and not stored. Also the subsequently "Calc. price" gets properly calculated and displayed and actually does the trick to lift PRODUCT related price to "product location customer" level. However the last step of revenue calculation does not work. From my pov this is related to the fact that the actuals revenue calculation insists to get the "calc. price" as a stored input KF for multiplying it w/ the QTY but there is actually no calc. price stored (> see planning objects) although this key figure is marked as "stored".

I am really keen to get to know why the "stored" flag doesn't work.

regards, Peter

P.S. I removed the "Calculated" flag from Calc. Price which also does not change the behaviour.

piyush_parekh
Active Contributor
0 Kudos

Hi Peter,

Can you please share screenshots of all key figures including validation? Looks like there is some confusion around dummy key figure. In your screenshot, you've mentioned LOCPRODCUSTWEEK. It should be LOCPRODCUSRCURRWEEK.

Regards,
Piyush