IBP - Local Member Configuration

[drbanning]

I am trying to create a Local Member to do inventory projections.

row 3 - has a local member I have named L BOH. The config I would like to work is

'=IF ( ISNULL ( 'SystemInventory" ) , PreviousMonth 'L EOH', 'SystemInventory')

so the calculation for C3 would be = IF ( ISNULL ( "C3" ), "B17" , "C3")

People will not be modifying the template once it is created, so I can use hard coded cell references if needed.

I am sure this is possible, but outside my wheelhouse - any assistance would be appreciated.

Thanks,

dan

[former_member186338]

Sorry, Dan!

I am unable to reproduce your formulas and your report! Test with something simple to isolate the issue...

In general - all local member statements like: EPMMEMBER, EPMSELECTEDMEMBERS, EPMALLMEMBERS, EPMTUPLE, EPMPOSITION are replaced with excel reference text and you can use Excel formulas with this text.

[former_member186338]

You have to use Excel references in local member (assuming that your report is static):

And to test for first column!

[drbanning]

The report is only somewhat static, since different planners will have different products quantities.

Your reference to the 'Column()=2' got me thinking and I added a simple 'Offset' to the calculation and got it to work.

=IF(P4=0, OFFSET(P21, 0, -1), P4)

which in the EPM sheet comes out to being -

=IF(EPMMEMBER([KEY_FIGURES].[].[DISINVTRADE])=0, OFFSET(EPMMEMBER(001), 0, -1), EPMMEMBER([KEY_FIGURES].[].[DISINVTRADE]))

While this works - I am running into different issues on the EOH calculation. Do I need to open a different question or am I allowed to continue down this thread? I get the Local BOH to calculate correctly, but as soon as I turn the Local EOH to reference the Local BOH, all local members disappear from the spreadsheet. I am wondering if this is due to a circular reference.

Referencing the picture 'LOCALMEMCALC' - Local EOH in cell P20 =

=IF(P4=0, P5-P8-P10+P14+P15+P16, P4-P8-P10+P14+P15+P16)

with the EPM sheet

=IF(EPMMEMBER([KEY_FIGURES].[].[DISINVTRADE])=0, EPMMEMBER([KEY_FIGURES].[].[BOH1])-EPMMEMBER([KEY_FIGURES].[].[DISSALESPRVFOC])-EPMMEMBER([KEY_FIGURES].[].[DISSALESPRVTRDPLUSCON])+EPMMEMBER([KEY_FIGURES].[].[DELIVERYINTRANSIT])+EPMMEMBER([KEY_FIGURES].[].[OPENSALESORDERPRVTRD])+EPMMEMBER([KEY_FIGURES].[].[BOOKINGSPRVTRD]), EPMMEMBER([KEY_FIGURES].[].[DISINVTRADE])-EPMMEMBER([KEY_FIGURES].[].[DISSALESPRVFOC])-EPMMEMBER([KEY_FIGURES].[].[DISSALESPRVTRDPLUSCON])+EPMMEMBER([KEY_FIGURES].[].[DELIVERYINTRANSIT])+EPMMEMBER([KEY_FIGURES].[].[OPENSALESORDERPRVTRD])+EPMMEMBER([KEY_FIGURES].[].[BOOKINGSPRVTRD]))

The instant I change the P5 reference to P6 - all local members disappear from the view.

thanks again for your help.

[former_member186338]

Please show screenshot of local member setup you have tested!

[drbanning]

I apologize, as soon as I wrote this question work threw me on a different project that has taken all my time until now. Here is a new screenshot.

The calculation of Local BOH - says if (P4=0, O20, P4) or if Disti Inv Prv Trade is empty, provide the previous month's EoH (Ending on Hand). As you can see in the calc window, it shows =if(P4=0, P20, P4) and as soon as I change P20 to O20, excel changes it back to P20.

Here is the local member calculation as seen from the EPM options

At the end of the day I am trying to do a psuedo replenishment order setup with a Local BOH, Local EOH and Local PO order. If I can get the Beginning on Hand sorted, i believe the rest will tied out quickly.

thanks,

dan