Determine if there is data in past 24 months of Actuals

[raf_lim]

Hello Experts,

We have a business requirement to flag if there is no longer data for the past 24 months in Actuals Key Figure. This is to flag if the record is still active or inactive. To achieve this, my idea is to bring the sum of the past 24 months into the current month of a new key figure. This can be done I think by following SAP Note 2240170. However, I am wondering if there is a better or more efficient way since the requirement is only to determine if the past 24 months is null/zero or if it has value and does not need to specific value.

Thank you in advance.

Best Regards,

Rafaelle Lim

[matthias_fitz]

Hi @raf_lim 

we had a similar request from business. Our approach to solve it was to first, make a new key figure. Within this key figure we manipulated the actuals, to either use the corresponding actuals in the past. If there were no actuals (isnull), we instead filled the key figure with a 0.

Next, we used an XYZ-segmentation. In the segmentation we used the manipulated actuals qty key figure mentioned above, added the requested horizon (in your case 24 months) with a calculation strategy "aggregate of periods", aggregation method "sum" and segmentation method "(1) by Threshold". By doing so, you can create a threshold in "smaller than or equal to" 0 which gives you a segment with no actuals, while evereything bigger than 0 has actuals.

Using a segmetation allows you to easily fill an attribute, that the end user can apply to filter in the excel add in or planner workspace.

If you have further questions (e.g. implementation screenshots), just reach out to me. I'm happy to help.

Best regards,
Matthias

[SubhojitC]

Hi Rafael,

Step 1: Create a Helper Keyfigure: ACTUALSAVAILABLEIN24

Step 2: Create an alert Keyfigure: ALERTACTUALS

Step 3: Add this KF ALERTACTUALS in Planning view as an Alert

Result: You will be able to see the Alert Keyfigure for that Product-Location-Customer combination which has Actual Qty in last 24 months

[raf_lim]

Hi @matthias_fitz ,

I am trying to leverage the XYZ Segmentation but I couldn't place a successful target attribute. As of now i used 24 copy operators to bring all months to the current month then have another new key figure to total all the actuals. I want to use the threshold in the XYZ Segmentation but I am having challenges in creating or choosing the right attribute. If you have any tips regarding this, please advise. Thank you so much

Regards,

Rafaelle

[matthias_fitz]

Hi @raf_lim,

luckily, this is a lot easier than using as many key figures as you've mentioned. In the following I'm adding the settings of our planning area, so you might need to make some adjustments. 

In this screenshot you see the general setup of the attribute.
We created an attribute for this usecase, with the proper attributes (please adjust it according to your needs). The measure is your actuals qty key figure, base plan level is derived from the actuals (so no inputs needed from your side), but you need to define the calculation level (this should be the same as the keys of the attribute).
Next, choose the periodicity past and 24 months in your case without offset.

This screenshot contains the logic of the xyz segmentation. Herein, you define to aggregate the periods by operator "SUM". That is essentially what you did with your 24 key figures (if I understood you correctly), that means, it sums up the past 24 months of the actuals key figure. By defining the segmentation "(1) by threshold", the system allows you to maintain any segments you like with any summed up value of your requirement.

As written in my previous answer, you need to provide a manipulated actuals key figure. The xyz segmentation by threshold is requiring a value greater than null. So if your actuals has only null values in the past 24 months, you will still not be able to segment it. What you need to do is to create a new key figure with a calculation like the one in the following screenshot. Possibly, a modelling with operator JF() would work as well, but I have not made any experiences on that. There @SubhojitC would might be able to assist you. 

In our case, we creating a segment with value "12", if the sum of the past 12 months is 0 or lower. We uses a number instead of a text, because we do not only make this segmentation once but three times for 12, 6 and 3 months without actuals, to provide a more flexible option to filter for business. 

I'm hoping this is giving you all the information necessary. If not, don't hesitate to reach out again.

Best regards,
Matthias