Issue with Local Members in Versions

[Former Member]

Dear experts,

I am currently experiencing an issue regarding local members when working with Versions in the Excel Planning Views.

In Version "Base Version" my Local Member behaves just fine. It is a simple calculation that I want to attach to a specific key figure for all planning level combinations in the planning view.

However, when I leave the Base Version and open a created version, the local member disappears. I entered the EPM Edit Report and tried to configure the local member to show up again, but it did not work for me.

Also, in the version I was not able to recreate the simple local member that works just fine in the Base Version. Whereas in the Base Version the Member is attached to every planning level combination, in the version it is simply put at the very bottom and only appears for the last respective planning level combination of the planning view.

Any guidance on how to deal with this issue? Thanks a lot!

Regards

Holger

[Irmi_Kuntze · Accepted solution]

Can you post some screenshots of both the EPM settings / formula and the excel planning sheet both working with Base and not working with alternative version so that you can see the excel references?

And which AddOn-Release are you?

[Former Member · Accepted solution]

Sure. We use EPM Addin 4.0.2.2 and our IBP is the StarterEdition on Version 4.

In the Base Version, I add an exemplary local member "(fn) Statistical Forecast Quantity" at the bottom of the planning combinations. As desired, after i press enter the local member is calculated at all planning combinations in the planning view:

In EPM, the local member looks like this:

When I switch the planning view to a version now, the local member disappears. In EPM the local member defintion did not change. I tried to edit the "attached to" definition (e.g. member combination with my Version also selected) but could not make my local member show up correctly again:

Now the second test: I recreate the same local member I created in the Base Version, this time in my SOP Version. As you can see on the screenshot, the member this time does only attach to the bottom planning combination on the screen and is not calculated for the other combination in the planning view:

In EPM, the local member looks like this:

I again tried to experiment with the "attached to" definitions, but was not able to create a universally applicable local member. When I e.g. change the "attached to" definition to the key figure Statistical Forecast Quantity (and optionally in combination with Version SOP), the local member disappears completely. I also noticed that the EPM formula does not change when you change the "attached to" definition and changed it manually, again without achieving my goal.

So basically after this analysis I have two questions:

a) Is it possible to design a universally applicable local member that shows up in both the base version and other versions without changing the local member settings?

b) If a) is not possible: How do I design local members in versions that at least behave the way like they do in the base version?

Thank you and best regards

Holger

[Irmi_Kuntze · Accepted solution]

Hi Holger

I guess I am lacking knowledge to answer your question, did not have the situation yet that I wanted to have local KF for both the simulation versions and the correct versions.

Until recently, we needed usually seperate planning views for boths anyhow as the chart for Versions where working differently.

So I tried a bit with it as I find this a very interesting question 🙂

If you dont change the way of display, you can have  local KF for the versions, but only if I attach it to member "version" and not member "key figure". Unfortunately than you get the line in every key figure and cannot choose to have it only for one version and one key figure.

___________________

But if you switch display order between key figure and version so that key figure is first, than I managed to display a local key figure and attach it to e.g. Consensus Demand. Than the local kf is displayed after each Consensus Demand for every Version in display, pretty well.

My lessons learned out of it is, that the local members always have to refer to the last colum before the grid - which is in standard version case the version and not the key figure

Working with formula such as =EPMPOSITION(5) does not return anything useful in this case.

Working with =EPM([SCNID].[].[UPSIDE]) - EPM([SCNID].[].[__BASELINE]) works if version is at the most right position - but than you get the local kf after EVERY kf.

Working with =EPMMEMBER([KEY_FIGURE].[].[CONSENSUSDEMAND]) works great, but only if the version is in the layout AFTER the key figure --> and in that case the formula still work when you remove the version !!!

So, it does work - if you switch display.

No switch, no formula with key figure but only with version and applying to all key figures

Hope that helps

Irmi

[Former Member · Accepted solution]

Thank you so much Imri, this helps a lot!

While experimenting further with local members I experienced that for me it was not possible to refer to other periods than the respective one. So, for a quick example, I could not design a local member that in April 2016 shows the Actuals Quantity of March 2016 - the formula of the local member always automatically switches from March back to April...

I know how to overcome an easy example like that with a regular key figure, but I thought a big advantage and main use case of local members was to overcome IBP's difficulties to perform calculations accross multiple time periods (at least visually in Excel). Did I miss something?

Best regards

Holger

[Irmi_Kuntze]

Maybe, as I have managed to calculate cross period in many cases for e.g. checking on todays months and do certain logic in the past and others in the future, or take values from previous months and roll over

Again, specific example with screenshot would help... 🙂

[Former Member]

Sure, but I am glad to hear that it should work - and I am pretty sure I used logics like you described before as well. I hope something is just off in my settings...

So here we go:

Case 1:

The most simple calculation I could think of: Have a local member that takes a certain value from the past period:

Surprisingly, even with local member recognition activated, IBP just performs a simple excel calculation and does not even build up a local member. This also holds for all kinds of other calculations, as long as the respective period (in this Case Jun'15) is NOT part of the formula:

Case 2:

Now I add the respective period to the formula. Again, a very basic calculation: Take Value of KF1 of the current period and add Value of KF2 of previous period:

When I press enter, the Local Member is created, but as I described the formula automatically switches to only include KFs in the respective period. Strange!

The created Local Member looks like this:

Im a little bit puzzled by this behaviour, because as you said and I remember, I was able to perform calculations like this before...

[Irmi_Kuntze]

Understand, and you are right.

When working with formulas such as

=EPMMEMBER([KEY_FIGURE].[].[CONSENSUSDEMAND])

I could not manage either, as I did not find any possibility to add time information.

But if you put in formula such as =J5+K6 it does work

Surely disadvantage is that you shall not change the order of the key figures as the system is stupid and would mess up.

Plus, you need to catch in the formula for the first period that the previous period does not have a value but text e.g. by something like this:

IF(IFERROR(INT(zelle)); calculation 1  ;  calculation 2)

Plus you can check if the kf is the right one by e.g.

=IF((ISNUMBER(SEARCH("Global Demand Planning Quantity Prev Cycle";$P6))); calculation 1; calculation 2)

$P6 would be the cell to check the kf name and with the dollar you make sure that alwyas the same column is checked and only the row is moved.

But in general for those kind of calculation my recommendation would be to tell the users for not changing the order of KF .

Alternatively you can write VBA macro for that, but that would be possible for real storage keyfigures that have editability.

--> See example:

--> in first period only K8 is taken to avoid error mesage.

Message was edited by: Irmhild Kuntze