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
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
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
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
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"
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
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.