Calculate balance in all month from opening and period sales

[former_member199448]

Dear Experts,

Sorry for disturbing you.

I am new in IP and i have  requirement to to create a planning function should calculate balance in all month from
opening and period sales.

In the aggregation level i have only 0sales (Sales for period) and in the cube i have opening Balance ZOPENBAL and cumulativ Balance 0BALANCE.

Could you please help me by providing me the appropriate code?

Do you have a doc or a book which could help to learn FOX?

Thank you in advance

Pat.

[former_member199573]

Hi Patrick,

As mentioned above, you would need to include the related infoproviders in a Multiprovider and create the aggregation level on top of it to use the key figures in the planning function.

I think that the balance key figures that you have per period can still be manipulated according to your requirement in the FOX to do your calculation.

Please let me explain with an example.

I want to calculate stock by a formula,

Stock = Stock in closed period + Production - Delivery

Here, I have Stock at the month level.

The production at day level and then the period characteristics are derived in the transformation while uploaded to cube.

Similarly, Deliveries.

Period range for planning.

B_FISCPER - beginning fiscal period - current period

E_FISCPER - Ending Fiscal period - Variable  - can be selected by user

Step-3. Calculation

FOREACH S_FISCPER.

   IF S_FISCPER >=B_FISCPER AND S_FISCPER <= E_FISCPER.

      S_FISCYEAR = SUBSTR(S_FISCPER,0,4).

      Z_KF1 = {ZPRQTY, S_FISCPER, S_FISCPER3, S_FISCYEAR}.

      Z_KF2 = {ZDELQT, S_FISCPER, S_FISCPER3, S_FISCYEAR}.

      Z_KF3 = 0.

      IF S_FISCPER = Y_FISCPER.

         Z_KF3 = {ZSTOCK, S_FISCPER, S_FISCYEAR}.

      ENDIF.

      IF Z_KF3 IS INITIAL.

         Z_KF3 = 0.

ENDIF.

Z_KF0 = Z_KF0 + Z_KF1 - Z_KF2 - Z_KF3.

      {ZSTOCK, S_FISCPER, S_FISCYEAR} = Z_KF0.

   ENDIF.

You will need to also create empty records first, between the start and end periods.

Create records with some dummy value and then make the key figure value 0.

Step-1

Create records with some dummy value

FOREACH S_FISCPER.

     S_FISCYEAR = SUBSTR(S_FISCPER,0,4).

          IF S_FISCPER >= Y_FISCPER.

                Z_KF6 = 9999.

                D_FISCPER = S_FISCPER.

                DO.

                    D_FISCPER = TMVL(D_FISCPER, 1).

                    D_FISCYEAR = SUBSTR(D_FISCPER,0,4).

                    {ZSTOCK, D_FISCPER, D_FISCYEAR} = Z_KF6.

         IF D_FISCPER >= B_FISCPER.

            EXIT.

         ENDIF.

      ENDDO.

   ENDIF.

ENDFOR.

Step-2

Make the key figure value as zero.

FOREACH S_FISCPER.

   IF S_FISCPER = Y_FISCPER.

      S_FISCYEAR = SUBSTR(S_FISCPER,0,4).

      Z_KF6 = 0.

      {ZSTOCK, S_FISCPER, S_FISCYEAR} = Z_KF6.

   ENDIF.

ENDFOR.

Please create your Multiprovider first by including the required cubes and then adjust the aggregation level.

Regards,

Raj.

[cornelia_lezoch]

Hi atrick,

as far as I know you can only use summable key figures in planning and no balance key figures.

So if you do not have a key figure in your aggregation level, you cn not use it in a planning function.

regards

Cornelia