Aggregation Considering Window - IBP

[Former Member]

Hey everyone,

We have a requirement here where we need to respect a window shipment. We use the key figure “Window – Sales Order” to represent this window, so when the KF is fulfilled with 0 means that’s a close window and when the KF is null means that it’s an open window.

I need to get my results based on open window, that means when I aggregate my buckets I need to show the result of the last open window.

For example, when I aggregate the technical week 37 I need to get the value of -1191013 which is the result of day 14.09 (my last open window of this week). Today the configuration it’s summing the values. (See image attached)

If I use LPA function, I’ll get the result of 0 which is the result of day 15.09 (last period of this week).

How can I aggregate bringing my last open window as result?

Kind regards,

Lorena

[piyush_parekh · Accepted solution]

Hi Lorena,

You can achieve this using attribute transformation and last period aggregation. The logic is slightly complicated and requires 6 transformations and multiple intermediate key figures.
Please find below illustration. For simplicity, let's assume TW37a consists of only 4 days.

Here, KF1 and KF2 need to be transformed by 1,2 and 3 periods. Different sets of key figures are used to populate transformed values. For e.g. Transform1_KF1 is 1 period offset of KF1; Transform1_KF2 is 1 period offset of KF2 and so on. (In your case, you would need 6 sets of key figures and attribute transformations)

In "Final KF", write below calculation -

Final KF = IF(ISNULL(KF1), KF2, IF(ISNULL(Transform1_KF1),Transform1_KF2, IF(ISNULL(Transform2_KF1),Transform2_KF2 ......., 0.999))))

Here, 0.999 represents the condition in which KF1 contains all 0 i.e. there is no open window. You can put any number as per business requirement. Use last period aggregation in Final KF to get last period's value at request level. Further, at request level of KF1, you can write,

KF1@request = Final KF@request

Hope this helps!!

Regards,

Piyush

[Former Member · Accepted solution]

Thanks so much for your help!!!