BI-IP Fox formula issue

[serbjeet_singh]

Hi

I have an issue with fox formula, the scenario is below.

We have 2 Planning DSOs, DSO1 and DSO2, with Multi Provider on top and aggregation level.

DSO1 which holds data for each product, cproduct , Range from and Range to, and Deviation %.

DSO2 holds transaction and planning data for product and cproduct.

there is one KPI called Control KPI in DSO2, which is calculated for each product and  Cproduct by a separate planning function.

Now Based on the control KPI value of Cproduct, i have to update  Deviation %. into  DSO2 from lookup by DSO1.

If only Control KPI value lies between Range from and Range to,  the same Deviation %.needs to be picked and updated into DSO2.

The records will look like in both DSOs something like this.

The fox code which i have written is below.

DATA PROD TYPE ZPROD.

DATA CPROD TYPE ZCPROD.

DATA CONTROL TYPE F.

DATA SU TYPE 0SALES_UNIT.

DATA GUID TYPE ZCAN_GUID.

DATA R1 TYPE F.

DATA R2 TYPE F.

* TO be changed selections --{ Key Figure Name ZPROD,0INFOPROV,0SALES_UNIT,ZCAN_GUID,ZCPROD }

FOREACH PROD, CPROD.

R1 = {ZFRM_PER,PROD,'DSO1',#,GUID,CPROD}.

R2 = {ZTO_PER,PROD,'DSO1',#, GUID ,CPROD}.

CONTROL = {ZCONT,# , 'DSO2',SU,#,CPROD}.

IF CONTROL > R1 AND CONTROL < R2.

{ZDEVPER,#,DSO2,SU,#,CPROD} = {ZDEVPER,PROD,DSO1,#,GIUID,CPROD}.

ENDIF. ENDFOR.

The issue here is basically three values in question , Control KPI , Range From and Range to are not read in one iteration.

They are read in three different iterations, that is when control KPI is read, then Range From and Range To is not read and vice versa, thus making comparison impossible

could anyone let me know, if any this wrong with code and suggest changes.

regards

[dheeraj_gupta · Accepted solution]

Hi Serbjeet,

I think I understand what you need.

Lets call DS02 = target DSO and DS01 = reference DSO.

You first need to put the target DSO in the filter. By this, you will be able to use FOREACH for Target DSO and FOREACH with REFDATA for the reference DSO.

First step would be to read the CONTROL KPI from the target DSO, using the command FOREACH (without REFDATA) and store it in a temporary variable. Make sure you use an IF statement just after FOREACH and before the read statement fot CONTROL KPIs which check  is CPRODUCT <> '  '. By doing this you will only read records where CPRODUCT is filled.

Now you need to another FOREACH loop with REFDATA to reach data from reference DSO.

Make sure you choose different variables for PRODUCT, CPRODUCT etc. Do not reuse the variables from the first foreach. <You will notice below that I have defined PROD, CANPROD etc twice> Within this foreach you much read the ranges, R1 ans R2.

Once you have read R1 and R2 you should check with an IF statement if R1 < CONTROL < R2.

If Yes, then you should read the DEV% KPIs and copy it into the target DSO.

DATA PROD TYPE 0CRM_PROD.
DATA PROD2 TYPE 0CRM_PROD.

DATA CANPROD TYPE ZCAN_PROD.

DATA CANPROD2 TYPE ZCAN_PROD.

DATA GUID TYPE ZCAN_GUID.

DATA GUID2 TYPE ZCAN_GUID.

DATA CONTROL TYPE F.

DATA R1 TYPE F.
DATA R2 TYPE F.

　

*reference DSO1

*target DSO2

.

*This Loops through target DSO.

FOREACH PROD,CANPROD,GUID.

IF CANPROD <> ' '.

   CONTROL = {ZJPBPCONT,#,DS02,#,CANPROD}.

*Loops through the reference DSO.

   FOREACH CANPROD2, PROD2, GUID2 IN REFDATA.

      IF CANPROD = CANPROD2.

        R1 = {ZFRM_PER,PROD2,DS01,GUID2,CANPROD2}.

        R2 = {ZTO_PER,PROD2,DS01,GUID2,CANPROD2}. 

          MESSAGE W006(B1) WITH 'YES' CONTROL R1 R2.

        IF CONTROL > R1 AND CONTROL < R2.

           {ZCANEFPER,#,DS02,#,CANPROD2} =    

                                                      {ZCANEFPER,PROD2,DS01,GUID2,CANPROD2}.
   
        ENDIF.
      ENDIF.

   ENDFOR.

ENDIF.

ENDFOR.

[former_member220155 · Accepted solution]

Hi Dheeraj,

Would it possible to read reference data from standard info cubes or it should be read only planning info cubes only. I have read from IC2 and update it into IC1 .So IC2 must be planning info cube or standard cube will also read. Please share ur thoughts.

Thanks in advance.

SG

[dheeraj_gupta · Accepted solution]

Hi SG,

Data that is not part of the target filter is considered as reference data. Does not matter whether the data is in a real-time or a standard cube.

If you want to reach reference data then you should use Foreach with ref.

Regards

[former_member220155 · Accepted solution]

Thanks for your reply Dheeraj.

My Requirement is:

In planning cube  IC1(Budget cube)  I have prodh1, prodh2 and  KF1 and in the standard cubes IC2(Actual) i have Prodh1, prodh2 and Kf2 and  stand cube IC3(Actual)  Prodh1, prodh2 and KF3

Compared to IC1 , in the standard info cubes IC2, and IC3 i have more Combinations of Prodh1, Prod H2

My requirement is:

I have to Generate the combinations of Prodh1, prodh2 in IC1 which is exist in IC2 and Ic3 and weighted factors of KF calcualtion also required for those Prodh1, Prodh2 combinations.

how to implement this solution in Fox formulas. Can you share your thoughts on this.

what is the best way to implement this

Create a planning cube IC4  on those two stand cubes Ic2 and IC3 , then create Multi provider on IC1 and IC4

or

Create directly Multiprovider on IC1 and, IC2, IC3 and do the FOX logic

which is best way.

and also can share some sample high level fox logic for this requirement.

I would appreciate for your help.

Thanks in advance

[former_member220155 · Accepted solution]

Hi Deeraj,

I have developed FOX code to read data from IC2 and assign it to IC1 it is just straight forward mapping but the data is not reading from second IC2. can you share your thoughts on this. we are in IP PAK. Both are planning cubes.

Ic1 = ZSCNNP20 (target cube)

Ic2 = ZSCNNP60 (Refernce cube)

In the Filter i resticted with target cube ZSCNNP20 (target cube)

DATA INFOPROV TYPE 0INFOPROV.

DATA FISCPER TYPE 0FISCPER.

DATA PRODH1 TYPE 0PRODH1.

DATA PRODH2 TYPE 0PRODH2.

DATA PRODH21 TYPE 0PRODH1.

DATA PRODH22 TYPE 0PRODH2.

DATA AMOUNT TYPE F.

  FOREACH PRODH1,PRODH2.

     FOREACH PRODH21,PRODH22 IN REFDATA.

BREAK-POINT.

       IF PRODH1 = PRODH21 AND PRODH2 = PRODH22.

      AMOUNT = {ZTRDGR,ZSCNNP60,PRODH21,PRODH22}.

      ENDIF.

    ENDFOR.

{0AMOUNT,ZSCNNP20,PRODH1,PRODH2} = AMOUNT.

ENDFOR.

[Anand71]

Hallo,

Try this:

To be changed { KF,ZPROD, INF, 0SAL, GUID}

FORECH GUID, ZPROD,0SAL.

R1 = {ZFRM, ZPROD,DSO1,#,GUID}

R2 = {ZTO,ZPROD,DSO!;#,GUID}.

CONTROL = { ZCOUNT, #,DSO2,0SAL,#}.

IF CONTROL > R1 AND CONTROL < R2.

{ZDEV,#,DSO2,0SAL,#} = {ZDEVPER,ZPROD,DSO1,#,GUID}

ENDIF.

ENDFOR.

Regards,

Anand Kumar

[cornelia_lezoch]

Hi Serbjeet,

it is not really clear, which data record you have in DSO2 befor the function is running.

do you have the pinkt data records - where CPROD = #? or the yellow where PROD = #?

Anyway - there lies the error in your function.

since in DSO1 each data record has PROD and CPROD filled you need to treat these data records as reference.

I would start the foreach loop over the data in DSO2 with

if {control ...} <>0.

...

endif.

then between the if and endif there comes the reading of reference from DSO1 and the real function - probably that is the function that you have written above.

regards

Cornelia

[serbjeet_singh]

Hi Cornelia

Thanks for the response.

Control KPI (ZJPBPCONT) values are calculated before this function is running.

Based on your comments i did some changes in my FOX as below, but still no data changed.

records are read but no changes.

DATA PROD TYPE 0CRM_PROD.

DATA CANPROD TYPE ZCAN_PROD.

DATA DS01 TYPE 0INFOPROV.

DATA DS02 TYPE 0INFOPROV.

DATA CONTROL TYPE F.

DATA SU TYPE 0SALES_UNIT.

DATA GUID TYPE ZCAN_GUID.

DATA R1 TYPE F.

DATA R2 TYPE F.

DATA L_CANEFPER TYPE F.

DS01= TCM1.

DS02= TPM1.

IF  {ZJPBPCONT,PROD,'DS02',SU,#,#} <>0.

IF  {ZJPBPCONT,#,'DS01',SU,#,CANPROD} <>0.

*FOREACH PROD,DS01,CANPROD IN REFDATA.

FOREACH PROD,DS01,CANPROD IN REFDATA.

CONTROL = {ZJPBPCONT,#,DS02,SU,#,CANPROD}.

FOREACH DS02,PROD,CANPROD.

  R1 = {ZFRM_PER,PROD,DS01,#,GUID,CANPROD}.

  R2 = {ZTO_PER,PROD,DS01,#,GUID,CANPROD}.

IF CONTROL >= R1 AND CONTROL <= R2.

L_CANEFPER =  {ZCANEFPER,PROD,DS01,#,GUID,CANPROD}.

ENDIF.

{ZCANEFPER,#,DS02,SU,#,CANPROD} =  L_CANEFPER.

ENDFOR.

ENDFOR.

ENDIF.

ENDIF.

Please suggest further.

thanks

serbjeet

[cornelia_lezoch]

Hi,

you need a foreach prod before the first IF.

so probably it should be:

foreach prod.

IF  {ZJPBPCONT,PROD,'DS02',SU,#,#} <>0.

CONTROL = ??? (I am not sure whether you should have another CONTROL definition, depends on what you want to read.

foreach CANPROD (in refdata only when these data records are not read by your filter)

R1 = {ZFRM_PER,PROD,DS01,#,GUID,CANPROD}.

  R2 = {ZTO_PER,PROD,DS01,#,GUID,CANPROD}.

IF CONTROL >= R1 AND CONTROL <= R2.

L_CANEFPER =  {ZCANEFPER,PROD,DS01,#,GUID,CANPROD}.

ENDIF.

{ZCANEFPER,#,DS02,SU,#,CANPROD} =  L_CANEFPER.

endfor.

endif. endfor.

regards

Cornelia

[cornelia_lezoch]

I assumed here, that the pink data records are the ones that are there berfore the function.

Otherwise you need to explain what is the difference between the pink and the yellow (in DSO2) and what you need those 2 records for.