Skip to Content
0

IBP Excel UI How to identify Planning Levels for a specified key figure node

Jun 02, 2017 at 11:14 AM

199

avatar image
Former Member

Hi IBP Experts,

is there a way, to identify the planning level for a specified key figure node (aka excel cell) to make it useable in an excel formula.

For example, I have a in cell M6 a value of 156 and want to get the values of the assigned planning level for this specific cell, that I can use this information in a different excel formula (something customer_ID = 1000, Product_ID = 1000).

Any idea how to achieve this?

Kind Regards

David

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Irmhild Kuntze
Jun 19, 2017 at 07:59 PM
0

This is an typical example for a calculated key figure in IBP backend, I would not try that in Excel at all

In backend you can easily work with aggregation levels which is more complicated in Excel, plus more performant

If for some unidentifiable reasons you would want to stick with Excel, you would need to work with VBA macro rather than EPM. But I would not recommend it

Share
10 |10000 characters needed characters left characters exceeded
Ivan Andreis
Jun 02, 2017 at 02:58 PM
0

Hi David,

I am afraid I didn't get your question, please can you share more details of what you are trying to accomplish? Maybe a screenshot as well.

Best regards,

Ivan

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Ivan,

please see attached screenshot

I'm looking for an excel formula (something like =EPMxxx) to find out for the value 246 (which is cell M6 in this example) the attributes assigned to this value (in this case Customer ID, which should give me back SBOX_CUST_1000 and PRODUCT_ID, which should give me back SBOX_1560 and the keyfigure to which this value is assigned.

That would be very helpful for some further calculations in Excel.

Thanks for your help.

Kind Regards

David

jssdt.png (9.8 kB)
0
Vadim Kalinin Jun 03, 2017 at 12:02 PM
0

There is no EPM function that will read properties of the data cell! You have to use something different to achieve "That would be very helpful for some further calculations in Excel." You have to explain: "further calculations"

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hello Vadim,

thanks for your answer. Always read your entries in the forum with interest and pleasure.

To comment on the further calculation, my example is rather coming from IBP Add-In which is a more crippled version of EPM for some calculations in IBP Supply.

I have an aggregate on level Ressource - Location and a key figure capacity supply.

I want now find out, if the value for this key figure is higher than a certain threshold and if so increase the costs for all products in that location. In order to do that I would need to find out for which location the capacity was increased.

My idea was to create a local member and store there the information from the aggregate for a specific cell, in this example M5.

And then on another tab read that information and compare the values for plant and if they are the same increase the value on another key figure (need again read Cell, get value for location and compare):

But maybe it was just an idea to being able to do that purely in Excel and instead we have to do that via key figures directly on the database.

Is that understandeable at all?

Thanks for helping out.

Kind Regards

David

d0txc.png (27.8 kB)
achvh.png (36.8 kB)
0

Unfortunately I am unable to clearly understand your idea. For sure it's possible to create local members with complicated excel formulas but you have to explain the required logic.

0