SAP BPC NW 10.1 Periodic PUSH to YTD Model

[ezequiel_bohrer3]

Hello:

I am running this script under SAP BPC NW 10.1 through a data manager package. (I owe you the SP)

I have Built a Logic that pushes from PERIODIC Model to YTD model as Follows:

The idea is to accumulate figures from PERIODIC Model and send it to a YTD model using Destination APP.

*DESTINATION_APP = YTD_MODEL

*WHEN CATEGORY

*IS PLAN

*WHEN TIME

*IS %TIME_SET%

*REC(EXPRESSION=%VALUE%, TIME = %TIME_SET%)

*ENDWHEN

*ENDWHEN

*DESTINATION_APP = YTD_MODEL

*WHEN CATEGORY

*IS PLAN

*WHEN TIME

*IS %TIME_SET%, TMVL(1,%TIME_SET%),

*REC(EXPRESSION=%VALUE%, TIME =TMVL(1,%TIME_SET%))

*ENDWHEN

*ENDWHEN

*DESTINATION_APP = YTD_MODEL

*WHEN CATEGORY

*IS PLAN

*WHEN TIME

*IS %TIME_SET%, TMVL(1,%TIME_SET%),TMVL(2,%TIME_SET%)

*REC(EXPRESSION=%VALUE%, TIME = TMVL(2,%TIME_SET%))

*ENDWHEN

*ENDWHEN

*DESTINATION_APP = YTD_MODEL

*WHEN CATEGORY

*IS PLAN

*WHEN TIME

*IS %TIME_SET%, TMVL(1,%TIME_SET%),TMVL(2,%TIME_SET%),TMVL(3,%TIME_SET%)

*REC(EXPRESSION=%VALUE%, TIME = TMVL(3,%TIME_SET%))

*ENDWHEN

*ENDWHEN

And so on for all the rest of the periods.

For some reason that i do not understand, when i pull a report in the YTD model with YTD measures all the values are properly calculated.

Now when i filter with PERIODIC MEASURES in the YTD MODEL I have

JAN, FEB, MAR, MAY,JUN,AUG,SEP, NOV an DEC OK

but APR = JAN + FEB + MAR + APR

And JUL = JAN + FEB + MAR + APR + MAY + JUN + JUL

And OCT = JAN + FEB + MAR + APR + MAY + JUN + JUL + AUG + SEP + OCT

Is there something functionally that i am missing or the script logic has something wrong?

Please let me know!

Thanks

But for every month the logic remains the same.

Any ideas what would cause this?

[former_member186338]

Can you explain the input parameters?

What do you have in %TIME_SET%?

Or do you want to copy full year?

Or...

Vadim

[ezequiel_bohrer3]

Hi vadim:

In %TIME_SET% i have 2016.01 in this example.

Thank you!

[former_member186338]

Hi ezequiel,

If you want to copy a single month YTD value (single month in %TIME_SET%) then:

*SELECT(%Y%,[YEAR],TIME,[ID]=%TIME_SET%) //Get Year in %Y%

*SELECT(%BP%,[BASE_PERIOD],TIME,[ID]=%TIME_SET%) //Get Base period in %BP%

*SELECT(%MNTHS%,[ID],TIME,[YEAR]=%Y% AND [CALC]=N AND [BASE_PERIOD]<=%BP%)

*XDIM_MEMBERSET TIME=%MNTHS% //Months from the beginning of the year!

*XDIM_MEMBERSET CATEGORY=PLAN

*DESTINATION_APP = YTD_MODEL

*WHEN TIME

*IS * //Scoped - months from the beginning of the year to (including) the selected month

*REC(EXPRESSION=%VALUE%, TIME = %TIME_SET%)

*ENDWHEN

BASE_PERIOD property has to be properly maintained (01,02,..,12)

Vadim

[ezequiel_bohrer3]

Hi vadim: thanks for you answer!

The script does a loop for the Whole year using a starting point %TIME_SET% = 2016.01 until the end of the year.

The XDIM MEMBERSET FOR TIME and the continuous TMVL copy from the first month until the whole year

*FOR %VAR_COUNT% = 0,1,2,3,4,5,6,7,8,9,10,11

*XDIM_ADDMEMBERSET TIME = TMVL(%VAR_COUNT%,%TIME_SET%)

*NEXT

Therefore it is not one single month, it is taking in the loop the whole year.

I would need to solve this  logic without using a property or a "dummy" Category.

I still do not get it why only in certain months, i have accumulated values in PERIODIC measure.

but for YTD it really works!

Please, let me know and thanks!!

Eze

[former_member186338]

Sorry - for the whole year it's a different story!

Once again, please describe the input!!!

What do you mean by "The script does a loop for the Whole year using a starting point %TIME_SET% = 2016.01 until the end of the year." ???

Starting point can be 2016.05 and till the end of the year? And in this case you don't want to copy 01,02,03,04?

Vadim

[ezequiel_bohrer3]

Vadim:

%TIME_SET% should have JAN.201X always as a premise. Also fore TMVL is going one month plus 2016.01. (Am i right?)

Then in the script every Month is adding from the PERIODIC model and sending it to the YTD model.

*DESTINATION_APP = YTD_MODEL

*WHEN CATEGORY

*IS PLAN

*WHEN TIME

*IS %TIME_SET%, TMVL(1,%TIME_SET%),TMVL(2,%TIME_SET%)

*REC(EXPRESSION=%VALUE%, TIME = TMVL(2,%TIME_SET%))

*ENDWHEN

*ENDWHEN

WITH 2016.01  = %TIME_SET% i have as a result:

*DESTINATION_APP = YTD_MODEL

*WHEN CATEGORY

*IS PLAN

*WHEN TIME

*IS 2016.01, TMVL(1,%TIME_SET%= 2016.02),TMVL(2,%TIME_SET% = 2016.03)

*REC(EXPRESSION=%VALUE%, TIME = TMVL(2,%TIME_SET%)=2016.03)

*ENDWHEN

*ENDWHEN

In this way i am adding/accumulating each month and copy it into the month itself.

YTD MODEL = will have in 2016.03 = 2016.01 + 2016.02 + 2016.03. Or at least that is what i am trying to accomplish.

Please let me know if this makes sense

Again thank you!!

[former_member186338]

"%TIME_SET% should have JAN.201X always as a premise. Also fore TMVL is going one month plus 2016.01. (Am i right?)" - ABSOLUTELY unclear!

Please forget for a second about your scripts with TMVL etc...

Please explain what you want to achieve:

When this script will be run?

What parameters will be specified by user?

What is the desired result?

Vadim

[ezequiel_bohrer3]

Vadim:

What i want to do here is to PUSH data from a PERIODIC, transform it to a YTD MEASURE and push it to a YTD model.

The script will be run thorugh data manager package.

The user will always inputs the YEAR.MONTH and the CATEGORY.

The result should push via destination APP from PERIODIC model, transform the data from PERIODIC model to YTD data and send it to the YTD Model.

That is what i need.

But in order to do so i can not use a propery neither a Dummy Category.

Can this be done using these premises?

[former_member186338]

User will input single month!

And what result you want to have in destination:

1. YTD value of this single month?

2. YTD values of some other months also?

or...

[ezequiel_bohrer3]

Hi Vadim:

Every Month has to be YTD in the destination Model.

Thanks!

[former_member186338]

"Every Month" ???? Every of what?

And what for user have to enter the single month?

In my sample code if user enter some month like 2016.05 then in the YTD model the sum of 2016.01+...+2016.05 will be written to the destination 2016.05

[ezequiel_bohrer3]

Vadim:

Every month from the year. As i told you, i can not use a Property in the dimension. it hasa to be done thorugh a script that accumulates values

If i choose 2015.02 then the PUSH has to transofrm and put in YTD:

2015.01 (YTD MODEL) = 2015.01 (PERIODIC MODEL)

2015.02 (YTD_MODEL) = 2015.01 (PERIODIC MODEL) + 2015.02 (PERIODIC MODEL)

If i choose 2015.03 then the YTD MODEL SHOULD HAVE

2015.01 (YTD MODEL) = 2015.01 (PERIODIC MODEL)

2015.02 (YTD_MODEL) = 2015.01 (PERIODIC MODEL) + 2015.02 (PERIODIC MODEL)

2015.03 (YTD MODEL) = 2015.01 (PERIODIC MODEL) + 2015.02 (PERIODIC MODEL) + 2015.03(PERIODIC MODEL)

This is what i want to achieve. And for YTD MEASURE it is working. But when i filter in PERIODIC MEASURE in the YTD MODEL some months are accumulated (descripted above) and i do not have a logical explanation for that behavior.

Thanks Vadim!

[former_member186338]

Test the following code in UJKT - doing what you want:

*SELECT(%Y%,[YEAR],TIME,[ID]=%TIME_SET%) //Get Year in %Y%

*SELECT(%TID%,[TIMEID],TIME,[ID]=%TIME_SET%) //Get TIMEID in %TID%

*SELECT(%M1%,[ID],TIME,[YEAR]=%Y% AND [CALC]=N AND [TIMEID]<=%TID%)

*SELECT(%M2%,[ID],TIME,[YEAR]=%Y% AND [CALC]=N AND [TIMEID]<=%TID%  AND [TIMEID]>%Y%0100)

*SELECT(%M3%,[ID],TIME,[YEAR]=%Y% AND [CALC]=N AND [TIMEID]<=%TID%  AND [TIMEID]>%Y%0200)

*SELECT(%M4%,[ID],TIME,[YEAR]=%Y% AND [CALC]=N AND [TIMEID]<=%TID%  AND [TIMEID]>%Y%0300)

*SELECT(%M5%,[ID],TIME,[YEAR]=%Y% AND [CALC]=N AND [TIMEID]<=%TID%  AND [TIMEID]>%Y%0400)

*SELECT(%M6%,[ID],TIME,[YEAR]=%Y% AND [CALC]=N AND [TIMEID]<=%TID%  AND [TIMEID]>%Y%0500)

*SELECT(%M7%,[ID],TIME,[YEAR]=%Y% AND [CALC]=N AND [TIMEID]<=%TID%  AND [TIMEID]>%Y%0600)

*SELECT(%M8%,[ID],TIME,[YEAR]=%Y% AND [CALC]=N AND [TIMEID]<=%TID%  AND [TIMEID]>%Y%0700)

*SELECT(%M9%,[ID],TIME,[YEAR]=%Y% AND [CALC]=N AND [TIMEID]<=%TID%  AND [TIMEID]>%Y%0800)

*SELECT(%M10%,[ID],TIME,[YEAR]=%Y% AND [CALC]=N AND [TIMEID]<=%TID%  AND [TIMEID]>%Y%0900)

*SELECT(%M11%,[ID],TIME,[YEAR]=%Y% AND [CALC]=N AND [TIMEID]<=%TID%  AND [TIMEID]>%Y%1000)

*SELECT(%M12%,[ID],TIME,[YEAR]=%Y% AND [CALC]=N AND [TIMEID]<=%TID%  AND [TIMEID]>%Y%1100)

*XDIM_MEMBERSET TIME=%M1% //all months including selected

*WHEN TIME.MONTHNUM

*IS 1

*FOR %N1%=%M1%

*REC(EXPRESSION=%VALUE%,TIME=%N1%)

*NEXT

*IS 2

*FOR %N2%=%M2%

*REC(EXPRESSION=%VALUE%,TIME=%N2%)

*NEXT

*IS 3

*FOR %N3%=%M3%

*REC(EXPRESSION=%VALUE%,TIME=%N3%)

*NEXT

*IS 4

*FOR %N4%=%M4%

*REC(EXPRESSION=%VALUE%,TIME=%N4%)

*NEXT

*IS 5

*FOR %N5%=%M5%

*REC(EXPRESSION=%VALUE%,TIME=%N5%)

*NEXT

*IS 6

*FOR %N6%=%M6%

*REC(EXPRESSION=%VALUE%,TIME=%N6%)

*NEXT

*IS 7

*FOR %N7%=%M7%

*REC(EXPRESSION=%VALUE%,TIME=%N7%)

*NEXT

*IS 8

*FOR %N8%=%M8%

*REC(EXPRESSION=%VALUE%,TIME=%N8%)

*NEXT

*IS 9

*FOR %N9%=%M9%

*REC(EXPRESSION=%VALUE%,TIME=%N9%)

*NEXT

*IS 10

*FOR %N10%=%M10%

*REC(EXPRESSION=%VALUE%,TIME=%N10%)

*NEXT

*IS 11

*FOR %N11%=%M11%

*REC(EXPRESSION=%VALUE%,TIME=%N11%)

*NEXT

*IS 12

*FOR %N12%=%M12%

*REC(EXPRESSION=%VALUE%,TIME=%N12%)

*NEXT

*ENDWHEN

Vadim

[ezequiel_bohrer3]

Hi vadim!

Thanks for the logic. With this logic, which is really fast, i am having the same result as with the logic i have built.

but APR = JAN + FEB + MAR + APR

And JUL = JAN + FEB + MAR + APR + MAY + JUN + JUL

And OCT = JAN + FEB + MAR + APR + MAY + JUN + JUL + AUG + SEP + OCT

Is the issue could be located somewhere else? and not in the logic?

Please let me know your thoughts

Ezequiel

[former_member186338]

But what is the issue???????

[ezequiel_bohrer3]

Vadim:

The issue is that when i Filter for PERIODIC measures in the YTD model i get accumulated values for 3 different months, described below.

MONTH (APR) = JAN + FEB + MAR + APR

JUL = JAN + FEB + MAR + APR + MAY + JUN + JUL

OCT = JAN + FEB + MAR + APR + MAY + JUN + JUL + AUG + SEP + OCT

All the rest of the month are ok

[former_member186338]

I am unable understand what is the issue! Please provide a clear report screenshot with data in both models...

"when i Filter for PERIODIC measures" - what do you mean by this?

in the YTD model

"i get accumulated values for 3 different months" - what do you mean by this?

described below.

Vadim

[ezequiel_bohrer3]

Vadim:

It means that when i pull the a report to check the values in the YTD model i filter MEASURES dimension by YTD member and the values are accumulated. THIS STEP IS OK. (your logic and mine)

Then if i filter the same report with PERIODIC member in dimension MEASURES in the same YTD model, and i have 3 months accumulated, For the other 9 months the data is Fine.

APR, JUL and OCT ONLY with accumulated values  for PERIODIC MEASURE. And i think that having 3 months with accumulated values for PERIODIC measure is no a correct result.

That is problem i am facing.

Regards!

[former_member186338]

Screenshot!! Are you able to post it? Instead of unclear text...

"Then if i filter the same report with PERIODIC member in dimension MEASURES in the same YTD model, and i have 3 months accumulated" - WHAT do you mean - accumulated?

[ezequiel_bohrer3]

Hi vadim this is the example i am getting when i pull the report under PERIODIC MEASURES.

[former_member186338]

Where I can see the real YTD figures for the same months on this picture?

[ezequiel_bohrer3]

Vadim these are the YTD Measures (which makes sense from my perspective)

[former_member186338]

OK, then you have an issue with PERIODIC measure on YTD cube. I remember issues like this - solved by BW and BPC upgrade to the latest SP!

What is your BW and BPC version/sp?

[former_member186338]

P.S. Do you have some non standard TIME members - like INP... Do you have standard year - JAN-DEC?

[ezequiel_bohrer3]

Hi Vadim. The time dimension is standard, no INP members in it.

[former_member186338]

Version/SP? For both!

[ezequiel_bohrer3]

Version is SAP BPC 10.1 NW , SPK-8100

[former_member186338]

Sorry, but practically each question I have to ask number of times...

BPC SP???

BW Version?

BW SP?

[ezequiel_bohrer3]

BPC SP = Support package ,SPK-8100 , SP-LEVEL 05

BW VERSION = 740

BW SP = SAPKW74010, SP-level 10

Is this information answer the question?

[former_member186338]

Current levels:

BPC 810 - SP07

BW740 - SP12

Also test PERIODIC measure on the standard YTD Consolidation model - fresh copy of ENVIRONMENTSHELL (just save some figures and look on the report with PERIODIC measure selected).

Vadim

[Former Member]

Hi Vadim,

I have the same error as described above.

1. push from periodic model to YTD model = OK

2. retrieval of YTD figures in YTD model    = OK

3. retrieval of periodic model in YTD model = INCORRECT

*PERIODIC formula from the BW (SE38 / uja_member_formula):

MEMBER [MEASURES].[YTD] AS 'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ"),-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])), ([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])))' SOLVE_ORDER=3

MEMBER [MEASURES].[PERIODIC] AS 'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP") AND NOT ([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="TOTAL" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q1" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="JAN" ), [MEASURES].[YTD]-([MEASURES].[YTD],[%TIME%].LAG(1)), [MEASURES].[YTD])' SOLVE_ORDER=3

**

BPC SP  = release 800, sp19

SAP_BW =release 740, sp 0010, SAPKW74010

[former_member186338]

Hi Tomas,

First - nothing related to data transfer PERIODIC -> YTD. You can test by manually updating YTD model and testing result.

Second - MEASURE formula is absolutely standard!

Third - try to update BW SP10 -> SP12!

Vadim

[Former Member]

Hi Tomas,

Same issue resolved after up gradation as Vadim suggested...

Aravind