Day to Technical Week conversion from S4 to IBP
Use case
There is a common requirement in IBP implementations to integrate data available in daily buckets from an operational system like S4 into technical weeks in IBP for planning at a higher level.
There are different ways to transform and load data from Days into Technical Weeks. I’ve looked at the available options and none are as efficient as the one below, therefore I would like to share the syntax in this post with you.
Example
Assume an example where we have the Actuals at Product, Customer and Day level in S4 and we would like to load in IBP into a Key Figure defined at Technical Week level.
In this case I’m using CPI-DS (HCI-DS) to generate a ProductCustomer combination joined with 100 rows of Date fields starting with the date of today to create a simple example.
In the Join query I have created 2 additional Fields called CWN and Month where I have calculated the Calendar week number in year and respectively the Month number in year. As we can see in Calendar week 44 there is a month split where we would like to group data into separate technical weeks and load them to IBP.
To achieve this we can use the syntax below in the mapping of the Date field.
"Join"."Sysdate" - least( day_in_week("Join"."Sysdate"), day_in_month("Join"."Sysdate") ) + 1
This will default every daily entry to the first day of the technical week as we can see below.
When loading to IBP, based on the aggregation rules defined by business, now we could either Select distinct or SUM the values using an aggregation query, group by technical week and load the Key Figure to IBP at its base planning level.
Hope this helps,
Cosmin
Hello Cosmin,
Conversion of date to Technical Week, can we be achieved by single code like below.
Assumption, I'm expecting the input date in yyyy.mm.dd format.
ifthenelse(
substr(Transform1.RequestDate,9,2) < substr(to_date(Transform1.RequestDate,'yyyy.mm.dd')-(day_in_week(to_date(Transform1.RequestDate,'yyyy.mm.dd'))-1),9,2),
to_date(substr(Transform1.RequestDate,1,4) ||'.'|| substr(Transform1.RequestDate,6,2)||'.01','yyyy.mm.dd'),
to_date(Transform1.RequestDate,'yyyy.mm.dd')-(day_in_week(to_date(Transform1.RequestDate,'yyyy.mm.dd'))-1))
with regards,
Nazeer
I like your single code. I worked great for me.
Hi Cosmin,
alternatively, you can aggregate the daily values to weeks in CPI-DS (HCI-DS) and have them disaggregated to technical weeks during import to IBP. To use this approach, you need to set the global variable $G_TIME_PROFILE_LEVEL to the time level of the loaded data (e.g. the weekly time level in this example). This is particularly relevant if you have customer specific period weights defined in IBP.
Kind Regards,
Thomas Kretz
Hi Thomas,
Thanks, this sounds really good. I did not see this method being implemented yet so I see 2 drawbacks at the moment but please correct me if I’m wrong:
Best regards,
Cosmin Pop
Hi Cosmin,
Disaggregating key figure values from Weeks to Technical Weeks is possible in IBP and a frequently used modelling approach. You mainly need to add a technical week level in the time profile and upload period weight factors defining the length of the technical weeks. You may have a look at the time profiles of the sample planning areas SAPIBP1 or SAP6 where this approach is used. There are also two related chapters in the IBP documentation
Your second concern is of course correct. If you disaggregate key figure values from calendar weeks to technical weeks in IBP, you will always use the period weights defined in IBP as proportional factors. If the data you load deviates from this distribution it may make more sense to do the aggregation to technical weeks before loading them to IBP.
Kind Regards,
Thomas
Great, thanks for sharing. I will need to try it once.
When I was looking for this info on forums I could only find bits and pieces.
Now both pre- and post-load aggregation methods are described in one place.
Best regards,
Cosmin
Hi Thomas,
Can you please explain the steps? How to achieve this using Global variables?
Hello, Could you explain how to calculate the field 'CWN'?