Disaggregation from Month to the first day of Technical Weeks

[vincentverheyen]

Dear experts,

I would like to ask your input on the best strategy to disaggregate a Key Figure on Month level to the first day of Technical Weeks.

• IBP: Perhaps one strategy is to use "Calendar functionality", as described in 3169595 - Using Calendar functionality to disaggregate to daily level considering calendar - SAP for....
• IBP: Another idea could use the "Period Weight Factor", as described in 2625311 - IBP: Consideration of Disaggregation Expression & Period Weight Factor - SAP for Me. In this case, what Period Weight do I add to my time Profile?
• IBP: A third way perhpas is to use a lot of custom Key Figures to do the disaggregation proportionally?
• CI-DS: use a QueryTransform such as in the code below:

least( day_in_week(...

• ... perhaps you have another preferred way?

The goal here is to have:

1. A split from Month to Technical Weeks according to the proportinality of the amount of working days in each Technical Week of that month.
2. For each of the quantities thus obtained on a Technical Week level, this needs to be land stored on the first day in each of those Technical Weeks.

It would be much appreciated if you could high-level describe your experience of the implementation method, considering the best approach in your experience.

[vincentverheyen · Accepted solution]

Let's say we have the following Key Figure KF1 (refer to irmhild.kuntze's answer regarding a detailed OSS note 3169595 for more details on how to set up a Calendar Key Figure in IBP, either via S/4 or via an IBP_CALENDAR which requires attaching a calendar to an extra attribute such as "ZPLANCALID", which in our case is attached to a Location):

• KF1 @ DAYLOC = IBP_CALENDAR("HELPERKEYFIGUREFORINPUT@DAYLOC","ZPLANCALID")

This yields us a 0 (for a non-working day) and a 1 (for a working day).

To determine the first day of the Technical Week in an algorithmic calculated Key Figure manner (thus not with a seperately manually maintained calendar as could be done via the Paste functionality of Mass Maintenance on IBP-side, again refer to mentioned OSS note, page 7), I came up with the following formulas:

• HKF2 = IBP_CAGGR("KF1@DAYLOC",''SUM'',''BACKWARD'',''CURRENTFUTURE'',2)

Where the last parameter "2" creates a reset on Technical Week (assuming our Time Period Level for Technical Week is 2).

This yields us with the total amount of working days on the first Day of the Technical Week. However, the issue is that this amount might also be equal to and present on the second Day &/or on the third Day &/or the xth consecutive ... Day of the Technical Week, in case the first Day &/or on the second Day &/or the x-1th consecutive ... Day is a non-working day.

To prevent this, we create the following PERIODSHIFT:

• HKF3 @ DAYLOC = IBP_PERIODSHIFT("HKF2@DAYLOC",1)

We can now compare if HKF2 (Day) is larger than HKF3 (Day minus 1), in which case we know there was a transition from one Technical Week to another.

• HK4 @ DAYLOC = IF("HKF2@DAYLOC">"HKF3@DAYLOC",1,NULL)

However, there are 2 cases in which HKF4 will fail to create a 1 on the first day of the Technical Week.

We can call these Case A) and Case B):

Case A)
The first case is where there are no working days whatsoever in the Technical Week, in which case we do not need to worry about not having a 1 in that Technical Week in HKF4, as we do not need to disaggregate to that Technical Week anyhow.

Case B)
The second case where HKF4 will fail to create a 1 on the first day of the Technical Week, is where we have only 1 working day in the Technical Week, and in the last day of the previous Technical Week there is also immediately a working day. In this case, the HKF4 comparison of the HKF3 PERIODSHIFT would not return a value of HKF2 being larger than HKF3, but instead they would be equal (namely value 1).

So case B is why we have to adapt the formula of HK4.

We first create an adaption of KF1 where we replace 0 by NULL:

• HKF5 @ DAYLOC = IF("KF1@DAYLOC"=0,NULL,"KF1@DAYLOC")

We also need an extra calculation level on WKLOC for KF1:

• KF1 @ WKLOC = SUM("KF1@DAYLOC")

Now we have enough information to be able to adapt the formula of HK4. Namely: if KF1 @ WKLOC is equal to 1, then that means that there is only 1 working day (⭐ which in the best case scenario is the first day of that Technical Week, thus in which case there will only be one 1 in HKF5, and it will be exactly on the first day of that Technical Week). So our final formula for HKF4 would be (formatted extra IF formula in italic for readability):

• HKF4 @ DAYLOC = IF("HKF2@DAYLOC">"HKF3@DAYLOC",1, IF("KF1@WKLOC"=1,"HKF5@DAYLOC",NULL) )

This now should yield us a 1 in HKF4, for every first Day of the Technical Week (here disregarding Case A since we do not want to disaggregate to such weeks anyhow).

To now have the amount of working days on those mentioned first Days, all we have to do is multiply our IBP_CAGGR function HKF2 with that HKF4:

• KF6 @ DAYLOC = "HKF2@DAYLOC" * "HKF4@DAYLOC"

⭐I will see below if I can adapt this, in case the single working day of that technical week was not on the first day of that technical week. We will call such a case Case C).

First, let us examine the results so far, for a standard example of Case B): let's examine Technical Week 48b 2023:

1. 1 (working day) on First day of Technical Week (1st of December 2023)
2. 0 (non-working day) on Second day of Technical Week (2nd of December 2023)
3. 0 (non-working day) on Third day of Technical Week (3th of December 2023)

In the case of case Case C) (which is a special case of Case B) where there first Day of the Technical Week is not the Day which holds the only Working Day of the Technical Week, we have to differentiate between Case C.1) and Case C.2).
Let's say Technical Week 48b 2023 looks as follows instead:

1. 0 (non-working day) on First day of Technical Week (1st of December 2023)
2. 1 (working day) on Second day of Technical Week (2nd of December 2023)
3. 0 (non-working day) on Third day of Technical Week (3th of December 2023)

We first examine Case C.1), where the last day of the previous Technical Week (30th of November 2023) is a working day:

Now, our KF6 is no longer on the first Day of the Technical Week (that is, in the Technical Week of the 1st of December to the 3rd of December 2023). How could we correct for this?

We could make a HKF7 where do a Cumulative Aggregation, similar to what we did in HKF2, but using itself as an input (that is, a Cummulative Aggregation on HKF2).

• HKF7 @ DAYLOC = IBP_CAGGR("HKF2@DAYLOC",''SUM'',''BACKWARD'',''CURRENTFUTURE'',2)
• HKF7 @ WKLOC = MAX("HKF7@DAYLOC")

We then, have to adjust our HKF4 formula such that, only where the value of HKF7 on Day level reaches the Maximum of HKF7 on Technical Week Level, we want the value of 1, or we could also say the value of HKF2 (namely 1).

We will also need an extra Helper Key Figure, since we can not include HKF7 twice on a different Planning Level, as input of the calculation of HKF4:

• HKF8 @ WKLOC = "HKF7@WKLOC"

So our final formula for HKF4 will be (formatted extra IF formula in italic for readability): ✅

• HKF4 @ DAYLOC = IF("HKF2@DAYLOC">"HKF3@DAYLOC",1, IF("KF1@WKLOC"=1, IF("HKF7@DAYLOC"="HKF8@WKLOC", "HKF2@DAYLOC",NULL) ,NULL) )

After these adjustments, we see that in Case C.1), our HKF4 and thus our final KF6 are indeed back on the first day of the Technical Week, as desired: ✅

There is also a Case C.2), where the last day of the previous Technical Week (30th of November 2023) is a non-working day.

Before our adjustments explained in Case C.1), the result of Case C.2) would be faulty (note HKF4 and thus our final KF6; that is, in the Technical Week of the 1st of December to the 3rd of December 2023):

After our adjustments explained in Case C.1), the result of Case C.2) are correct (note HKF4 and thus our final KF6; that is,in the Technical Week of the 1st of December to the 3rd of December 2023) as well: ✅

HKF5 is fully optional and not necessary for the calculation. If you notice any bug during testing, please let me know.

So far as I can see, the above is an algorithmic way to determine the first day of the Technical Week and thus create correct proportional factors to disaggregate (the disaggregation itself from Month to Day can be done using a copy operator on Month level, with the target key figure of 0 decimals, to prevent decimal numbers which would appear if you would do a disaggregation by a calculated key figure split, that is: if not using a copy operator).

If you prefer a manual way instead to determine the first day of the Technical Week (also: beware for mistakes when manually maintaining such a calendar), one could opt for a Planning Calendar maintained in IBP or in S/4, or manually create Period Weights in your time profile.

In case we are in a Technical Week, where we have already passed the first day of the Technical Week, then the CURRENTFUTURE (instead of PASTCURRENTFUTURE) from the formulas above, will mean that we will not have any proportional factor in the current week. To prevent this, we could adapt our formula for KF6. Before it was:

• KF6 @ DAYLOC = "HKF2@DAYLOC" * "HKF4@DAYLOC"

After adjustments, it could be (formatted extra IF formulas in italic for readability):

• KF6 @ DAYLOC = IF("PERIODID7"="$$PERIODID7CU$$",IF("PERIODID0"="$$PERIODID0CU$$",""HKF2@DAYLOC",NULL), "HKF2@DAYLOC" * "HKF4@DAYLOC")

Or even more easy:

• KF6 @ DAYLOC = IF("PERIODID0"="$$PERIODID0CU$$",""HKF2@DAYLOC","HKF2@DAYLOC" * "HKF4@DAYLOC")

This will give us only the remaining working days in the current Technical Week, on the Today's bucket.

[Irmi_Kuntze]

Hi Vincent

if you want to go with WORKING days, those would require a calendar and hence you cannot work with any kind of WEEKWEIGHT or similar as that is attribute to the overall time profile.

For your requirement, the OSS note will 3169595 work: It splits by the calendar you define (on whatever planning level you want to have the calendar, easiest would be on location level), it does not put the value on a non-working-day, and it does a proportional split based on working days.

So why trying CI-DS loads if you have the calendar anyhow in your system and all information for a key figure calculation?

There might be other calculation possibilities than the one given by the OSS note, or variants of that. But I think at least the part with grabbing the calendar with simplified key figure calculation is the best way, however you use that then for the disaggregation.

Yours, Irmi

[vincentverheyen]

Thank you irmhild.kuntze, this is much appreciated. I will read the note this weekend, it seems to be a meticulously documented note at first sight.

[vincentverheyen]

I am now able to use the IBP_CALENDAR function, but the disaggregation to the first day of the technical week level. How would you do this?

Currently, I was trying to create the Proportional Factor, via a first Helper Key figure which has Cummulative Aggregation function (on the IBP_CALENDAR Key Figure) with options SUM, BACKWARDS, CF, 2 (Period ID of Technical Week... to reset).

And then a second Key Figure where you only take the MAXIMUM (that is by checking if the Day is not lower than the maximum of the Technical Week level), such that you end up with the aggregation on the first working day of that Technical Week.

However, in case you have a holiday (non-working day) on the first day of the Technical Week, you might end up - contra intention - with a duplicate of the same maximum on 2 days in the same Technical Week, so you need to work with a PeriodShift, and compare. I think the calculations might become a bit trickier if you start having 2 or 3 holidays after one another, as you can shift the numbers from the previous Technical Week into the following Technical Week, when doing the comparison using PeriodShift.

[Irmi_Kuntze]

Hey Vincent

have you really read the note? That is all described with formula and screenshots.

And you need to certainly maintain bank holidays in your calendar

Irmi

[vincentverheyen]

irmhild.kuntze, it was my impression that the note explains an equal split to every working day in the calendar. Is this not what we see on the second image of page 24? You see this clearly in Calendar "08" as there are multiple working days per Technical Week, in that Calendar. Calendar "SYP1" and Calendar "ZW" are misleading, because they only have 1 working day per week.

This however, does not provide a solution for the question at hand, which is: it is easy for me to calculate the quantity of working days in a Technical Week; however, it does not seem easy to have this said whole quantity to be placed fully on the first day of the Technical Week. I do not see this in the Note. How do I determine the first day of the Technical Week? I am thinking now, perhaps I should try to use Last Period Aggregation, to determine the last day of the previous Technical Week, which I can then Periodshift 1 month to find the first day of the Technical Week? Or perhaps use IBP_LAST_VALUE? If not, I only see possibilities to use CI-DS to determine the first day of the week, or a manually maintained calendar in S4, or a manually maintained calendar in IBP, or a manual weight in the Time Period Profile.

Do you refer to and propose to use the manual maintenance of a Calendar using the Mass Maintenance described on Page 7?

[riyazahmed_ca2]

Hi vincent.verheyen

The Tech-Week definition is basically refers to the first day of that period. Technically speaking, it don't have any other day of that tech-week associated with it except for the PERIODEND attribute which we hardly refer in our calculations. Hence by default, Month to tech-week disaggregation is happening only to the first days of the tech-weeks of that month.

We can download this using CI-DS in tech-week level and the TSTFR will also refers to the same first days of the weeks.

Am I missing something?

Best Regards,

'Riyaz'

[vincentverheyen]

Hi riyazahmed.ca2, can you clarify please what you mean with TSTFR? And in your view, is CI-DS a necessity for this disaggregation?

In my time Time Profile, I give you an example of a Technical Week "TW16 2023":

• TPLEVEL 2
• PERIODID 20253
• PERIODSTART 2023-04-17 12:00:00 AM
• PERIODEND 2023-04-23 11:59:59 PM
• WEEKWEIGHT 7

Can you clarify a bit on the exact process you propose to disaggregate from Month Level? I would like to understand better and test.