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
- SAP Managed Tags:
8 Comments
| User | Count |
|---|---|
| 2 | |
| 2 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |
