Fox formula to calculate data with posting period offsets

[former_member246680]

Hi experts,

I have a requirement to create a function where, when the user selects one posting period, the system, should go on and calculate data from the next period until the end of the fiscal year. For example, a user wants to sum up data from period 003 in version 0, with data from version 1 period 004 - 012. So when the user selects period 003, the system should automatically sum up that with period 004 - 012 data. The same applies when the use selects data for another period, say period 005 of version 0, the system automatically sums it up with data for periods 006-012 for version 1.

TOTAL = {period005,version0} + {period006-012,version1}.

I had suggested creating customer exits which automatically pick the next periods until the end of the fiscal year but the user wants the requirement to be done by fox. Could you kindly assist with the formula which automatically offsets to the next period until the end of the year.

Many thanks

Jordan

[cornelia_lezoch]

Hi Jordan,

you need a DO loop.

period = VARV(...).

Do.

period = tmvl(period, 1)

put you calculation here.

if period = 12. exit.

endif.

enddo.

regards

Cornelia

[former_member246680]

Hi Cornelia,

I have written the code as follows:

DATA PL TYPE F.

DATA BS TYPE F.

DATA RESULT TYPE F.

DATA GL TYPE 0GLACCOUNT.

DATA VER TYPE 0VERSION.

DATA VERS TYPE 0VERSION.

DATA YEARTO TYPE 0FISCYEAR.

DATA YEARFR TYPE 0FISCYEAR.

DATA PERFR TYPE 0FISCPER3.

DATA PERTO TYPE 0FISCPER3.

DATA OPER TYPE 0FISCPER3.

PERFR = VARV( 0FISCPER_1).

IF VERS = '#'.

FOREACH PERFR, YEARFR, GL.

DO. 

PERFR = TMVL( PERFR, 1 ).

BS = { 0BALANCE, '007', YEARFR, GL, # }.

PL = PL + { 0SALES, PERFR, YEARFR, GL, # }

.

IF PERFR = '012'.   EXIT. 

ENDIF.

RESULT = BS + PL. 

FOREACH OPER IN VARIABLE OPEN_PERIODS.

{ 0BALANCE, OPER, YEARTO, GL, '001' } = RESULT.

ENDFOR.

ENDDO.     

   ENDIF.

It reads some data but does not generate any records. The 0balance should come from one period in a fiscal year, and the 0sales should come from the next periods until the end of the year. The summation of 0balance and 0sales should be written to 12 periods in the following year. I hope you can follow the explanation.

Regards

Jordan                           

[cornelia_lezoch]

Hi Jordan,

I put some comments on the functions

PERFR = VARV( 0FISCPER_1). - looks o.k.

IF VERS = '#'. why you need this? it´s defined in the next steps again

FOREACH PERFR, YEARFR, GL. perfr can only be a single value, so it´s not necessary in the foreach.

where do you tell the system what the value is for yearfr?

Are you running the function for several years???

DO.

PERFR = TMVL( PERFR, 1 ).

BS = { 0BALANCE, '007', YEARFR, GL, # }. why is 007 defined as fixed value? if this value is for one period, put it before the do-loop

PL = PL + { 0SALES, PERFR, YEARFR, GL, # }

.

IF PERFR = '012'.   EXIT.

ENDIF.

RESULT = BS + PL. put this after the do-loop oherwise result will be to high

FOREACH OPER IN VARIABLE OPEN_PERIODS.

{ 0BALANCE, OPER, YEARTO, GL, '001' } = RESULT. put this after the do-loop

ENDFOR.

ENDDO.    

   ENDIF.

the do-loop is only to go through the records and calc the sum for PL. Do nothing else within this loop.

regards

Cornelia

[former_member246680]

Hi Cornelia,

Thanks a lot.

I have a variable for fiscal year which i select before running the function, so i select the source fiscal year and target fiscal year. I fixed period to ‘007’ because that’s where I want source the 0sales data from. When I select source period ‘007’ for 0SALES data, the formula should then calculate 0balance data from periods ‘008’ up to ‘012’. For now the formula is reading 0sales values but is not able to read 0balance values even though they are present in the cube.

After putting information messages, the formula is also reading values for periods 001-011 instead of 007-012.

The formula is now as follows:

DATA PL TYPE F.

DATA BS TYPE F.

DATA RESULT TYPE F.

DATA GL TYPE 0GLACCOUNT.

DATA VER TYPE 0VERSION.

DATA VERS TYPE 0VERSION.

DATA FISPF TYPE 0FISCPER.

DATA FISP TYPE 0FISCPER.

DATA YEARTO TYPE 0FISCYEAR.

DATA YEARFR TYPE 0FISCYEAR.

DATA PERFR TYPE 0FISCPER3.

DATA PERTO TYPE 0FISCPER3.

DATA OPER TYPE 0FISCPER3.

DATA CUBE TYPE 0INFOPROV.

*VARIABLES

VER = VARV( Z_VER ).

VERS = VARV( S_VER ).

YEARTO = VARV( YR_TO ).

YEARFR = VARV( YR_FROM ).

PERFR = VARV( 0_FISCPER3_1).

FOREACH YEARFR, GL, CUBE IN REFDATA.

FISPF = CONCAT (YEARFR, PERFR).

DO. 

PERFR = TMVL( PERFR, 1 ).

BS = { 0BALANCE, FISPF, '007', YEARFR, CUBE, GL, # }. 

PL = PL + { 0SALES, FISPF, PERFR, YEARFR, CUBE, GL, # }.

  IF PERFR = '012'.   EXIT. 

ENDIF.

ENDDO.

RESULT = BS + PL.

FOREACH OPER IN VARIABLE OPEN_PERIODS.

FISP = CONCAT(YEARTO, OPER).

{ 0BALANCE, FISP, OPER, YEARTO, ZCUBE, GL, '001' } = RESULT.

ENDFOR.

ENDFOR.  

Regards

Jordan

[cornelia_lezoch]

so is there any open question?

[former_member246680]

Hi Cornelia,

yeah, the formula still fails to generate records and it also fails to offset the period values from 007-012.

Regards

Jordan

[cornelia_lezoch]

do I see it right that you have 0fiscper, 0fiscper3 and 0fiscyear in your fields to be changed?

This is hard to work with.

Ude either 0fiscper OR 0fiscper3 and 0fiscyear in your aggregation level.

otherwise you do loop and the offset needs to create offests for 0fiscper AND 0fiscper3.

also this line

{ 0BALANCE, FISP, OPER, YEARTO, ZCUBE, GL, '001' } = RESULT.

probably needs to be in the end, outside of the foreach ... inrefdata loop.

regards

Cornelia

[former_member246680]

Hi Cornelia,

I have written the code as follows:

*Data declarations

DATA .......TYPE....

....       .......

  TABLE LT_IP { I_IP TYPE 0INFOPROV KEY, I_NON_KEY TYPE I }.

*Constants FV = 'K4'.

COA = 'CASB'.

IT = '#'.

*VARIABLES

VER = VARV( ZFM_VER ).

VERS = VARV( YSRC_VER ).

CURR = VARV( 0P_CURRF ).

YEARTO = VARV( YSS_GLS_TO ).

YEARFR = VARV( YSS_GLS_FROM ).

LV_FP = CONCAT( YEARFR, '001' ).

*Fill internal table with info providers

FOREACH YEARFR, JNID, SID, PERFR, GL, VERS, CUBE, VAL. 

IF CUBE <> 'YRBPCGLPL'. 

LT_IP.{ I_NON_KEY, CUBE } = 1. 

ENDIF.

ENDFOR.

FOREACH YEARFR, JNID, SID, PERFR, GL, VERS, CUBE, VAL.

IF VERS = '003' OR VERS = '004' OR VERS = '#'.

FISPF = CONCAT( YEARFR, PERFR ).

NXT_PERFR = TMVL( PERFR, 1 ).    

NXT_FISPF = CONCAT( YEARFR, NXT_PERFR ).

  LV_BS = { 0BALANCE, COA, CURR, FISPF, PERFR, 'K4', YEARFR, CUBE, JNID, SID, GL, '#', VAL, IT }.   

IF CUBE <> 'YRBPCGLPL'.

*loops at cubes   

CLEAR LV_PL.     

FOREACH CUBE IN LT_IP.

*Gets foreach value  

DO.         

LV_PL = LV_PL + { 0SALES, COA, CURR, NXT_FISPF, NXT_PERFR, 'K4', YEARFR, CUBE, JNID, SID, GL, VERS, VAL, IT }.

       NXT_PERFR = TMVL( NXT_PERFR, 1 ).  

        NXT_FISPF = CONCAT( YEARFR, NXT_PERFR ).

   IF NXT_PERFR = 001. 

EXIT.           ENDIF.         ENDDO.     

ENDFOR.      

LV_SUM = LV_BS + LV_PL.

*Post result to period 001 up to 012 next fiscal year 

  FOREACH OPER IN VARIABLE YCE_OPEN_PERIOD_MS.       

FISP = CONCAT( YEARTO, OPER ).

{ 0BALANCE, COA, 00, FISP, OPER, 'K4', YEARTO, YRBPCGLPL, 'J_CA011', BPC_CP, GL, '001', '025', IT } = LV_SUM.      

ENDFOR.     ENDIF.   ENDIF.

ENDFOR.     

When i debug it, it is collecting the data from the 2 versions as required, and sums it up, and gives the correct summation at ( LV_SUM = LV_BS + LV_PL.) But when i want to post that result to periods 001 - 012, the system seems to throw the value away and nothing is generated. This is at          { 0BALANCE, COA, 00, FISP, OPER, 'K4', YEARTO, YRBPCGLPL, 'J_CA011', BPC_CP, GL, '001', '025', IT } = LV_SUM. If i put the result as a numerical figure in the code, for example: LV_SUM = 100 + 200., the system will post correctly, but if i change it to  LV_SUM = LV_BS + LV_PL., it fails to post. The 0fiscper,0fiscper3 and 0fiscyear are working  correctly. Its the posting which has a problem.

Where do you think i am getting it wrong?

Regards

Jordan

[cornelia_lezoch]

Hi Jordan,

sorry, the code is to long, I lost track.

But why are those

ENDIF.   ENDIF.

ENDFOR.    

in the end of the code?

Which loops are closing there?

Especially the endfor seems not to be right.

make sure, that you do not have several loops creating again the 0balance and overwriting the result from a former loop.

or better - take the result calcualtion out and put it after other loops.

regards

Cornelia

[former_member246680]

Hi Cornelia, Thanks for your response. The ENDIF. ENDIF. ENDFOR. are supposed to have been like: ENDIF. ENDIF.   ENDFOR. Let me try apply your advice. Thanks a lot. Regards Jordan

[former_member246680]

Hi Cornelia,

Sorry its the editor. So its supposed to be

ENDIF.

  ENDIF. 

     ENDFOR.

Regards Jordan

[gregor_dieckmann]

Hi Jordan,

why to use FOX? In a query one can just use a variable for period, create two restricted key figures

A: version 0, variable for period

B: version 1, Interval variable for period + 1 to 12

F: formula A + B

Regards,

Gregor

[former_member246680]

Hi Gregor,

Thanks for your response. I am using fox to write the data from one infocube to another, and the process of copying from one infocube to another involves the calculation as explained above. The query can make the calculation but wont be able to move the data to another infoprovider.

Regards

Jordan