Local member issue

[Former Member]

Hi Experts,

I have used local member for some calculation. Using this local member i want to perform some calculation and want to store the derived number. for ex:

Dec 14         Dec  14     Dec adj               2014.P13      Jan 15

Forecast        Actual      (Actual-forecast)  Forecast         Q4F

50                  100               50                    200              250(50+200)

From the above , Dec Adj is the local member with formula (Actual-Forecast). And i want to sum the Dec Adj number+2014.P13 and post into Jan15 and i want to save this Jan 15 number.

If user update the number for 2014.P13 then the jan15 will calculate automatically. Please help .

Please let me know if you require any other details. Thanks in advance.

Kind regards,

Srikant

[former_member186338 · Accepted solution]

Hi Srikant,

The idea of local members - is to use it in reporting, not for data saving!

You can use EPMSaveData in the local member, but I recommend to think about other methods...

Like script logic etc...

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Thanks for your reply.

I have written some  logic to post the value into 2015.Jan.however i am getting the below validation error.

*XDIM_MEMBERSET ACCOUNT = BAS(BUD2197)

*XDIM_MEMBERSET TIME=2014.P09

*XDIM_MEMBERSET CATEGORY=DECFCAST

*XDIM_MEMBERSET RPTCURRENCY=LC

*XDIM_MEMBERSET AUDITTRAIL=Input

*WHEN ACCOUNT

*IS *

*WHEN CATEGORY

*IS DECFCAST

*REC(EXPRESSION=(%VALUE%-([CATEGORY].[ACTUAL],[TIME].[2014.P09],[AUDITTRAIL].[SAP-BW]))+ ([TIME].[2014.P13],[CATEGORY].[Q4F],[AUDITTRAIL].[Input]),TIME=2014.P10,CATEGORY=Q4F,AUDITTRAIL=Input)

*ENDWHEN

*ENDWHEN

If i validate the above script i am getting the error "Attribute [SAP-BW] does not exist in dimension "AUDITTRAIL".

I have attached the script and audittrial members. Please help me on this.

Thanks.

Srikant

[former_member186338 · Accepted solution]

It's a bad idea to use ID like SAP-BW

Use SAP_BW instead!

Vadim

P.S. The script is not correct in general. Can you explain the calculation logic?

[Former Member · Accepted solution]

Hi Vadim,

the calculation logic is

Dec 14         Dec  14               2014.P13      Jan 15

Forecast        Actual                 Q4F               Q4F

50                  100                        200              (100-50)+200=250

I want to post  ((December actual-December Forecast)+2014.P13 ) this value into Jan 15 Q4F.

Thanks.

Regards,

Srikant

[former_member186338 · Accepted solution]

Have you changed the AUDITTRAIL ID?

[Former Member · Accepted solution]

Hi Vadim,

No, to change the ID i need to discuss with others as well. I can't delete this  Id because we have historical data for this ID. Is there any other alternative for this?

Thanks.

Regards,

Srikant

[former_member186338 · Accepted solution]

Sorry, but during script logic processing the system for some SP levels "-" inside member ID can be treated as "minus"... It's better to avoid the ID's with "-"

You code have to be something like:

*XDIM_MEMBERSET ACCOUNT = BAS(BUD2197)

*XDIM_MEMBERSET TIME=2014.P09,2014.P13

*XDIM_MEMBERSET CATEGORY=DECFCAST,ACTUAL,Q4F

*XDIM_MEMBERSET RPTCURRENCY=LC

*XDIM_MEMBERSET AUDITTRAIL=Input,SAP_BW

*WHEN CATEGORY

*IS ACTUAL

*WHEN TIME

*IS 2014.P09

*WHEN AUDITTRAIL

*IS SAP_BW

*REC(EXPRESSION=%VALUE%,TIME=2014.P10,CATEGORY=Q4F)

*ENDWHEN

*ENDWHEN

*IS DECFCAST

*WHEN TIME

*IS 2014.P09

*WHEN AUDITTRAIL

*IS Input

*REC(EXPRESSION=-%VALUE%,TIME=2014.P10,CATEGORY=Q4F)

*ENDWHEN

*ENDWHEN

*IS Q4F

*WHEN TIME

*IS 2014.P13

*WHEN AUDITTRAIL

*IS Input

*REC(EXPRESSION=%VALUE%,TIME=2014.P10,CATEGORY=Q4F)

*ENDWHEN

*ENDWHEN

*ENDWHEN

Accumulation will generate a correct result in the target.

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Many thanks for your help. Thanks a lot.

I tried the code what you have provided except the audit trail ID(I have changed SAP_BW to  SAP-BW).

Its not picking up the Dec-14 actuals. Is this only because of audittrial ID SAP-BW? or do i need to change any code in the logic.

Dec 14          Dec14            2014.P013          Jan-15

DecFcast      Actual              Q4F                    Q4F

500                 400                5000                (400-500)+5000=4900.

However i am getting 4500 for Jan 15. Please advise.

Thanks again.

Kind regards,

Srikant

[former_member186338 · Accepted solution]

Yes, looks like it's because of audittrial ID SAP-BW.

Assuming that for ACTUAL you don't have any other data then in SAP-BW, try to remove AUDITTRAIL condition at all:

...

*IS ACTUAL

*WHEN TIME

*IS 2014.P09

*WHEN AUDITTRAIL

*IS SAP_BW

*REC(EXPRESSION=%VALUE%,TIME=2014.P10,CATEGORY=Q4F)

*ENDWHEN

*ENDWHEN

Vadim

[former_member186338 · Accepted solution]

UPS, sorry!

In this case it's not because of id, you have to override target AUDITTRAIL for ACTUAL:

*IS ACTUAL

*WHEN TIME

*IS 2014.P09

*WHEN AUDITTRAIL

*IS SAP-BW

*REC(EXPRESSION=%VALUE%,TIME=2014.P10,CATEGORY=Q4F,AUDITTRAIL=INPUT)

*ENDWHEN

*ENDWHEN

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Yes. It works now. Thank you so much for your time and help.

[Former Member · Accepted solution]

Hi Vadim,

One query regarding I want to include audittrial Eliminations to this.

*XDIM_MEMBERSET ACCOUNT = BAS(BUD2131)

*XDIM_MEMBERSET TIME=2015.P09,2015.P13

*XDIM_MEMBERSET CATEGORY=DECFCAST,ACTUAL,Q4F

*XDIM_MEMBERSET RPTCURRENCY=LC

*XDIM_MEMBERSET AUDITTRAIL=Input,SAP-BW,

*WHEN CATEGORY

*IS ACTUAL

*WHEN TIME

*IS 2015.P09

*WHEN AUDITTRAIL

*IS SAP-BW

*REC(EXPRESSION=-%VALUE%,TIME=2015.P10,CATEGORY=Q4F,AUDITTRAIL=Input)

*ENDWHEN

*ENDWHEN

*IS DECFCAST

*WHEN TIME

*IS 2015.P09

*WHEN AUDITTRAIL

*IS Input

*REC(EXPRESSION=%VALUE%,TIME=2015.P10,CATEGORY=Q4F)

*ENDWHEN

*ENDWHEN

*IS DECFCAST

*WHEN TIME

*IS 2015.P13

*WHEN AUDITTRAIL

*IS Input

*REC(EXPRESSION=%VALUE%,TIME=2015.P10,CATEGORY=Q4F)

*ENDWHEN

*ENDWHEN

*ENDWHEN

From the above script i am getting the below numbers.

Sales;  Q4F              Actual                        Actual               Decfcast

           Input             sap bw                       Eliminations        Input

           2015.P10        2015.P109                   2015.P09         2015.p09

            -150                    150                             30                  0

I want 150+30 =180 in Q4F but i am getting 150.

Can you please help.

[former_member186338 · Accepted solution]

Sorry but your code is incorrect:

You have:

*WHEN CATEGORY

*IS ACTUAL

...

*IS DECFCAST

...

*IS DECFCAST //Incorrect! Duplicate!

...

And please provide a READABLE table with values!

And where is Eliminations in code???

Vadim

[Former Member · Accepted solution]

Hi Vadim,

Sorry for that, please see below

the calculation logic is

Dec 15             Dec  15           Dec15               2015.P13      Jan 15(2015.P10)

Decforecast        Actual        Actual                    Decfcast             Q4F

Input                   sap-bw         Eliminations         input

50                      100                    30                            200            50-(100+30)+200=120

Dec-15(DecFcast) – Dec-15(Actual) + Jan16(DecFcast)

Can you please help me with this. please let me know if this is not clear.

Thanks.

Regards,

Srikant

[former_member186338 · Accepted solution]

Sorry, the table is unreadable!

Please create a table in Excel and post screenshot!

Use real dimension names and real member ID's (not descriptions}!

[former_member186338 · Accepted solution]

As an alternative you can create table directly in the advanced editor:

Dimension	A	B	C	D	E
TIME
CATEGORY
AUDITTRAIL
Value	50	100	30	200	50-(100+30)+200=120

[Former Member · Accepted solution]

Hi vadim,

Please see below.

Hope this is clear. Thanks.

[Former Member · Accepted solution]

missed columns in pic.

see below.

Sorry for the confusion. Thanks.

[former_member186338 · Accepted solution]

Absolutely easy:

*XDIM_MEMBERSET ACCOUNT = BAS(BUD2131)
*XDIM_MEMBERSET TIME=2015.P09,2015.P13
*XDIM_MEMBERSET CATEGORY=DECFCAST,ACTUAL
*XDIM_MEMBERSET RPTCURRENCY=LC
*XDIM_MEMBERSET AUDITTRAIL=Input,SAP-BW,Eliminations

*WHEN TIME
*IS 2015.P09
  *WHEN CATEGORY
  *IS ACTUAL
    *WHEN AUDITTRAIL
    *IS SAP-BW,Eliminations
      *REC(EXPRESSION=-%VALUE%,TIME=2015.P10,CATEGORY=Q4F,AUDITTRAIL=Input)
    *ENDWHEN
  *IS DECFCAST
    *WHEN AUDITTRAIL
    *IS Input
      *REC(EXPRESSION=%VALUE%,TIME=2015.P10,CATEGORY=Q4F)
    *ENDWHEN
  *ENDWHEN
*IS 2015.P13
  *WHEN CATEGORY
  *IS DECFCAST
    *WHEN AUDITTRAIL
    *IS Input
      *REC(EXPRESSION=%VALUE%,TIME=2015.P10,CATEGORY=Q4F)
    *ENDWHEN
  *ENDWHEN
*ENDWHEN

Vadim

[former_member186338 · Accepted solution]

By the way I hope that my code is correct, because on the screenshot:

And strange to see AE11 and AY11 near D11 and E11...

[Former Member · Accepted solution]

Hi vadim,

Thanks for the script. it works well.

One more issue, I got correct number in LC, but if i run the currency conversion for Q4F,2015.P10

this calculation is not working.

for ex:Actual conversion for Euro is 1.42106

          Decfcast is          for Euro    1.44001.

the value in lc

After running currency conversion

If i check the calculation for LC it seems fine, however for Euro the this calcuation does not work.

Is this because of different currency's for different category's.

can you please help me on this?

[former_member186338 · Accepted solution]

If my answers are helpful - mark it helpful.

Second - currency conversion is not related to the original question - please open a new discussion and provide complete explanation.

Vadim

[Shrikant_Jadhav]

Hi Srianth,

In addition to Vadim, you can use content override form formatting sheet or epmcopy range, IF TEMPLATE COLUMN STRUCTURE IS FIXED.

Shrikant