cancel
Showing results for 
Search instead for 
Did you mean: 

Example FOX formula top-down distribution based on reference data

Former Member
0 Kudos

Hello,

we use IP 7.0 and are looking for an example of a FOX formula which performs a top-down distribution using actual data as reference data (multi-provider combines actual and planning cube). The reference data however should be selected with a variable "period from" - "period to" and an average value should be returned from the actuals (so if three periods selected -> calculate average of three periods from actuals and use it for top-down planning data. Does anyone already did this before?

thanks

Dries

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

2 steps :

1/ Summation

2/ Distribution

In my example I don't do a top-down distribution...

*{Key Figure Name,Posting period,Fiscal year,Version,Value type,Plng Area}

*********************************************************

                                      • DATA DECLARATION ********************

*********************************************************

DATA PLANAREA TYPE AREA____ .

DATA RATIO TYPE KEYFIGURE_NAME.

DATA FLAG TYPE 0PLANTCAT.

DATA PERIOD TYPE 0FISCPER3.

DATA PERIODF TYPE 0FISCPER3.

DATA PERIODT TYPE 0FISCPER3.

DATA PER001 TYPE 0FISCPER3.

DATA PER002 TYPE 0FISCPER3.

DATA PER003 TYPE 0FISCPER3.

DATA PER004 TYPE 0FISCPER3.

DATA PER005 TYPE 0FISCPER3.

DATA PER006 TYPE 0FISCPER3.

DATA PER007 TYPE 0FISCPER3.

DATA PER008 TYPE 0FISCPER3.

DATA PER009 TYPE 0FISCPER3.

DATA PER010 TYPE 0FISCPER3.

DATA PER011 TYPE 0FISCPER3.

DATA PER012 TYPE 0FISCPER3.

DATA LASTPER TYPE 0FISCPER3.

DATA ACTUYEAR TYPE 0FISCYEAR.

DATA LASTYEAR TYPE 0FISCYEAR.

DATA FCSTYEAR TYPE 0FISCYEAR.

DATA BUDGYEAR TYPE 0FISCYEAR.

DATA SUMBUDG TYPE F.

DATA SUMFCST TYPE F.

DATA ACTU001 TYPE F.

DATA ACTU002 TYPE F.

DATA ACTU003 TYPE F.

DATA ACTU004 TYPE F.

DATA ACTU005 TYPE F.

DATA ACTU006 TYPE F.

DATA ACTU007 TYPE F.

DATA ACTU008 TYPE F.

DATA ACTU009 TYPE F.

DATA ACTU010 TYPE F.

DATA ACTU011 TYPE F.

DATA ACTU012 TYPE F.

*********************************************************

                                                • PROCESS ************************

*********************************************************

FOREACH RATIO.

*$----

-

-

-

-

-


$*

$ BUDGET $

*$----

-

-

-

-

-


$*

*$----

-

-

-

-

-


$*

$ Summation $

*$----

-

-

-

-

-


$*

PLANAREA = VARV('UHISTORY').

BUDGYEAR = VARV('ZYEARRAG').

ACTUYEAR = TMVL(BUDGYEAR,-1).

LASTPER = VARV(FIRSTFCT).

PERIODF = VARV(ZPERIOFR).

PERIODT = VARV(ZPERIOTO).

IF LASTPER <= PERIODF.

LASTYEAR = TMVL(BUDGYEAR,-2).

PER001 = PERIODF.

ELSEIF LASTPER <= PERIODT.

LASTYEAR = TMVL(BUDGYEAR,-2).

PER001 = PERIODT.

PERIODT = PERIODF.

ELSE.

LASTYEAR = TMVL(BUDGYEAR,-1).

PER001 = PERIODF.

ENDIF.

IF PERIODT > PER001.

PER002 = TMVL(PER001, 1) .

ENDIF.

IF PERIODT > PER002.

PER003 = TMVL(PER001, 2) .

ENDIF.

IF PERIODT > PER003.

PER004 = TMVL(PER001, 3) .

ENDIF.

IF PERIODT > PER004.

PER005 = TMVL(PER001, 4) .

ENDIF.

IF PERIODT > PER005.

PER006 = TMVL(PER001, 5) .

ENDIF.

IF PERIODT > PER006.

PER007 = TMVL(PER001, 6) .

ENDIF.

IF PERIODT > PER007.

PER008 = TMVL(PER001, 7) .

ENDIF.

IF PERIODT > PER008.

PER009 = TMVL(PER001, 😎 .

ENDIF.

IF PERIODT > PER009.

PER010 = TMVL(PER001, 9) .

ENDIF.

IF PERIODT > PER010.

PER011 = TMVL(PER001,10) .

ENDIF.

IF PERIODT > PER011.

PER012 = TMVL(PER001,11) .

ENDIF.

SUMBUDG = 0 .

ACTU001 = 0 .

ACTU002 = 0 .

ACTU003 = 0 .

ACTU004 = 0 .

ACTU005 = 0 .

ACTU006 = 0 .

ACTU007 = 0 .

ACTU008 = 0 .

ACTU009 = 0 .

ACTU010 = 0 .

ACTU011 = 0 .

ACTU012 = 0 .

PERIOD = PER001.

DO.

IF PERIOD = PER001 .

ACTU001 = {RATIO,PERIOD,LASTYEAR,000,010,PLANAREA}.

ENDIF.

IF PERIOD = PER002 .

ACTU002 = {RATIO,PERIOD,LASTYEAR,000,010,PLANAREA}.

ENDIF.

IF PERIOD = PER003 .

ACTU003 = {RATIO,PERIOD,LASTYEAR,000,010,PLANAREA}.

ENDIF.

IF PERIOD = PER004 .

ACTU004 = {RATIO,PERIOD,LASTYEAR,000,010,PLANAREA}.

ENDIF.

IF PERIOD = PER005 .

ACTU005 = {RATIO,PERIOD,LASTYEAR,000,010,PLANAREA}.

ENDIF.

IF PERIOD = PER006 .

ACTU006 = {RATIO,PERIOD,LASTYEAR,000,010,PLANAREA}.

ENDIF.

IF PERIOD = PER007 .

ACTU007 = {RATIO,PERIOD,LASTYEAR,000,010,PLANAREA}.

ENDIF.

IF PERIOD = PER008 .

ACTU008 = {RATIO,PERIOD,LASTYEAR,000,010,PLANAREA}.

ENDIF.

IF PERIOD = PER009 .

ACTU009 = {RATIO,PERIOD,LASTYEAR,000,010,PLANAREA}.

ENDIF.

IF PERIOD = PER010 .

ACTU010 = {RATIO,PERIOD,LASTYEAR,000,010,PLANAREA}.

ENDIF.

IF PERIOD = PER011 .

ACTU011 = {RATIO,PERIOD,LASTYEAR,000,010,PLANAREA}.

ENDIF.

IF PERIOD = PER012 .

ACTU012 = {RATIO,PERIOD,LASTYEAR,000,010,PLANAREA}.

ENDIF.

IF {RATIO,PERIOD,LASTYEAR,000,010,PLANAREA} <> 0.

SUMBUDG = SUMBUDG +

{RATIO,PERIOD,LASTYEAR,000,010,PLANAREA}.

ENDIF.

IF PERIOD = PERIODT .

  • End Processing

EXIT.

ELSE.

  • Next Period

PERIOD = TMVL(PERIOD, 1).

  • Jump to next Year if necessary

IF PERIOD = '013'.

PERIOD = '001'.

LASTYEAR = ACTUYEAR.

ENDIF.

ENDIF.

ENDDO.

*$----

-

-

-

-

-


$*

$ Distribute by prior 12 months $

*$----

-

-

-

-

-


$*

BUDGYEAR = VARV('ZYEARRAG').

ACTUYEAR = TMVL(BUDGYEAR,-1).

LASTPER = VARV(FIRSTFCT).

PERIODF = VARV(ZPERIOFR).

PERIODT = VARV(ZPERIOTO).

IF LASTPER <= PERIODF.

LASTYEAR = TMVL(BUDGYEAR,-2).

PER001 = PERIODF.

ELSEIF LASTPER <= PERIODT.

LASTYEAR = TMVL(BUDGYEAR,-2).

PER001 = PERIODT.

PERIODT = PERIODF.

ELSE.

LASTYEAR = TMVL(BUDGYEAR,-1).

PER001 = PERIODF.

ENDIF.

IF PERIODT > PER001.

PER002 = TMVL(PER001, 1) .

ENDIF.

IF PERIODT > PER002.

PER003 = TMVL(PER001, 2) .

ENDIF.

IF PERIODT > PER003.

PER004 = TMVL(PER001, 3) .

ENDIF.

IF PERIODT > PER004.

PER005 = TMVL(PER001, 4) .

ENDIF.

IF PERIODT > PER005.

PER006 = TMVL(PER001, 5) .

ENDIF.

IF PERIODT > PER006.

PER007 = TMVL(PER001, 6) .

ENDIF.

IF PERIODT > PER007.

PER008 = TMVL(PER001, 7) .

ENDIF.

IF PERIODT > PER008.

PER009 = TMVL(PER001, 😎 .

ENDIF.

IF PERIODT > PER009.

PER010 = TMVL(PER001, 9) .

ENDIF.

IF PERIODT > PER010.

PER011 = TMVL(PER001,10) .

ENDIF.

IF PERIODT > PER011.

PER012 = TMVL(PER001,11) .

ENDIF.

DO.

IF SUMBUDG <> 0.

IF PERIOD = PER001 .

{RATIO,PERIOD,BUDGYEAR,099,020,COPA00T1} =

( ACTU001 / SUMBUDG ) *

{RATIO, #,BUDGYEAR,099,020,COPA00T1}.

ENDIF.

IF PERIOD = PER002 .

{RATIO,PERIOD,BUDGYEAR,099,020,COPA00T1} =

( ACTU002 / SUMBUDG ) *

{RATIO, #,BUDGYEAR,099,020,COPA00T1}.

ENDIF.

IF PERIOD = PER003 .

{RATIO,PERIOD,BUDGYEAR,099,020,COPA00T1} =

( ACTU003 / SUMBUDG ) *

{RATIO, #,BUDGYEAR,099,020,COPA00T1}.

ENDIF.

IF PERIOD = PER004 .

{RATIO,PERIOD,BUDGYEAR,099,020,COPA00T1} =

( ACTU004 / SUMBUDG ) *

{RATIO, #,BUDGYEAR,099,020,COPA00T1}.

ENDIF.

IF PERIOD = PER005 .

{RATIO,PERIOD,BUDGYEAR,099,020,COPA00T1} =

( ACTU005 / SUMBUDG ) *

{RATIO, #,BUDGYEAR,099,020,COPA00T1}.

ENDIF.

IF PERIOD = PER006 .

{RATIO,PERIOD,BUDGYEAR,099,020,COPA00T1} =

( ACTU006 / SUMBUDG ) *

{RATIO, #,BUDGYEAR,099,020,COPA00T1}.

ENDIF.

IF PERIOD = PER007 .

{RATIO,PERIOD,BUDGYEAR,099,020,COPA00T1} =

( ACTU007 / SUMBUDG ) *

{RATIO, #,BUDGYEAR,099,020,COPA00T1}.

ENDIF.

IF PERIOD = PER008 .

{RATIO,PERIOD,BUDGYEAR,099,020,COPA00T1} =

( ACTU008 / SUMBUDG ) *

{RATIO, #,BUDGYEAR,099,020,COPA00T1}.

ENDIF.

IF PERIOD = PER009 .

{RATIO,PERIOD,BUDGYEAR,099,020,COPA00T1} =

( ACTU009 / SUMBUDG ) *

{RATIO, #,BUDGYEAR,099,020,COPA00T1}.

ENDIF.

IF PERIOD = PER010 .

{RATIO,PERIOD,BUDGYEAR,099,020,COPA00T1} =

( ACTU010 / SUMBUDG ) *

{RATIO, #,BUDGYEAR,099,020,COPA00T1}.

ENDIF.

IF PERIOD = PER011 .

{RATIO,PERIOD,BUDGYEAR,099,020,COPA00T1} =

( ACTU011 / SUMBUDG ) *

{RATIO, #,BUDGYEAR,099,020,COPA00T1}.

ENDIF.

IF PERIOD = PER012 .

{RATIO,PERIOD,BUDGYEAR,099,020,COPA00T1} =

( ACTU012 / SUMBUDG ) *

{RATIO, #,BUDGYEAR,099,020,COPA00T1}.

ENDIF.

IF {RATIO,PERIOD,BUDGYEAR,099,020,COPA00T1} = 0 .

{RATIO,PERIOD,BUDGYEAR,099,020,COPA00T1} =

{RATIO, #,BUDGYEAR,099,020,COPA00T1} / 12.

ENDIF.

ELSE.

{RATIO,PERIOD,BUDGYEAR,099,020,COPA00T1} =

{RATIO, #,BUDGYEAR,099,020,COPA00T1} / 12.

ENDIF.

IF PERIOD = PERIODT .

  • Réinit Spread value

{RATIO, #,BUDGYEAR,099,020,COPA00T1} = 0.

SUMBUDG = 0.

  • End Processing

EXIT.

ELSE.

  • Next Period

PERIOD = TMVL(PERIOD, 1).

  • Jump to next Year if necessary

IF PERIOD = '013'.

PERIOD = '001'.

LASTYEAR = ACTUYEAR.

ENDIF.

ENDIF.

ENDDO.

Good work !

Ramak

Former Member
0 Kudos

Raphael,

You should replace some of your your IF statements for each of 12 months with some Do loops. It woudl be much easier to maintain the FOX.