Skip to Content

IBP - Local Member Configuration

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

localmembercalc.jpg (234.1 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Nov 20, 2018 at 07:21 AM

    Please show screenshot of local member setup you have tested!

    Add comment
    10|10000 characters needed characters exceeded

    • 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

      localmemcalc.jpg (494.3 kB)
      localboh.jpg (94.3 kB)
  • Jan 27 at 06:36 AM

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

    And to test for first column!

    Add comment
    10|10000 characters needed characters exceeded

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

  • Jan 27 at 06:30 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded