Look-up Functionality for KF Caluclation

[Former Member]

Demand planner enters values for "Sales Forecast" key figures for a combination of product-customer.

If the Sales Forecast is greater than 1000, then a key-figure called "Revenue Segment" should be computed as "High" ...if the Sales Forecast is greater than 501 and less than 1000, then "Revenue Segment" should become "Medium"

Calculation Logic: A custom caluclated key-figure "Revenue Segment" should compare the values of 'sales forecast' and needs to do a look-up in to the below table/profile...and checks the boundaries of Sales Forecast Min and Max and throw the result as "high, medium and low".

Business Need: In every S&OP cycle, they want to plot analytics for "High" revenue segments only.

Requirements:

i) Finance team decides these "Min and Max". So the table updates.

ii) Can the Revenue segment keyfigure calculation do this look-up ?

@FOrum, please throw any solution for this

[former_member268877 · Accepted solution]

Hi Venkat,
One way I can think of is to use ABC segmentation.
As you have limits maintained by users in a key figure for Product-Customer combination,
you can use standard segmentation app to configure segmentation rules based on this KF.

Once you run segmentation job it will fill product attribute with values high,medium or low.

Regards,
Ujwal

[ankpatel]

Hi Venkat

There is no way you can do lookup functionality within IBP calculations. One way to do is via VBA code in Excel.

Regards

Ankur

[Former Member]

Thanks Ankur,

You mean to say, with the VBA code, the resultant calculated values can be saved in to key-figures i,e on to S4 ?

I know the Local Member concept in the Excel EPM formatting, but those values remain local and you can't plot any analytics with those values and cant integrate them on to other systems.

Can you throw more light on this please

best Regards

venkat

[ankpatel]

Hi Venkat

I don't think VBA code will save data in the key figure. I thought you just want to have lookup functionality for the analytics. I don't think there is any other way to do lookup and save that data in Key figure.

Regards

Ankur