on 06-02-2017 12:14 PM
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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"
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
15 | |
4 | |
3 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.