cancel
Showing results for 
Search instead for 
Did you mean: 

BI IP Average calculation

Former Member
0 Kudos

Hi experts,

I have a question about IP and fox.

My need is to calculate an average score and create a record in a cube to store the result of the average calculation.

I will try to explain what I except in my cube. For my business, we want to analyse forms in BW and as I am new on BI IP I thought I can do evaluation with IP to have more flexbility than using data loading transformations.

Here is my data in my cube :

ZIDFORM is a unique number of form.

ZINTQUES is  a group of questions

ZINTREPO is a response of a question (compound with ZINTQUES).

ZEVALUATION is a floating number with an evaluation.

0CALMONTHZIDFORMZINTQUESZINTREPOZEVALUATION
05.20136156Q003R0016
05.20136156Q003R00214
05.20136156Q003R00320
05.20136156Q003R0046
05.20136156Q004R00120
05.20136156Q004R00220
05.20136156Q004R0030
05.20136157Q003R0010
05.20136157Q003R0020
05.20136157Q003R0030
05.20136157Q003R0040
05.20136157Q004R0016
05.20136157Q004R0020

I want to know if it's easy to have this result after an execution of a formula in fox. The line added have # in ZINTREPO because I assume that the average of each group of questions are store in the # value for ZINTREPO.

0CALMONTHZIDFORMZINTQUESZINTREPOZEVALUATION
05.20136156Q003R0016
05.20136156Q003R00214
05.20136156Q003R00320
05.20136156Q003R0046
05.20136156Q003#11,5
05.20136156Q004R00120
05.20136156Q004R00220
05.20136156Q004R0030
05.20136156Q004#13,33333333
05.20136157Q003R0010
05.20136157Q003R0020
05.20136157Q003R0030
05.20136157Q003R0040
05.20136157Q003#0
05.20136157Q004R0016
05.20136157Q004R0020
05.20136157Q004R0036
05.20136157Q004#4

I tried this fox code but this code doesn't work.

DATA ZZIDFORM TYPE ZIDFORM.

DATA ZZINTREPO TYPE ZINTREPO.

DATA ZZINTQUES TYPE ZINTQUES.

DATA ZSUM TYPE F.

DATA ZCOUNT TYPE I.

BREAK-POINT.

FOREACH ZZIDFORM.

    ZCOUNT = 0.

  FOREACH ZZINTQUES.

    ZCOUNT = 0.

    FOREACH ZZINTREPO.

      IF ZZINTREPO = '#'.

      ELSE.

        MESSAGE I000 (Z_SPECIF_BW) WITH ZZINTQUES ZZINTREPO.

        ZSUM = ZSUM + {ZNOTE,ZZIDFORM,ZZINTQUES,ZZINTREPO}.

        ZCOUNT = ZCOUNT + 1.

      ENDIF.

    ENDFOR.

    IF ZCOUNT = 0.

      {ZNOTE,ZZIDFORM,ZZINTQUES,'#'} = 0.

    ELSE.

      {ZNOTE,ZZIDFORM,ZZINTQUES,'#'} = ZSUM / ZCOUNT.

    ENDIF.

  ENDFOR.

ENDFOR.

If you have any suggestions, they will be welcomed.

Regards,

Jérôme

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Jerôme,

in your code zsum is not reset, You can try :

DATA ZZINTREPO TYPE ZINTREPO.

DATA ZSUM TYPE F.

DATA ZCOUNT TYPE I.

ZSUM = 0.

ZCOUNT = 0.

FOREACH  ZZINTREPO  IN REFDATA.

IF (NOT ZZINTREPO IS INITIAL) .

ZCOUNT =   ZCOUNT +1 .

ZSUM  = ZSUM  + { ZEVALUATION, ZZINTREPO}.

ENDIF.

ENDFOR.

{ ZEVALUATION, #} = ZSUM / ZCOUNT.

only ZZINTREPO is to be changed

Hope it helps

Former Member
0 Kudos

Hi Anass,

thanks a lot !!! It seems to work perfectly but in my case, I have some evaluation at 0. Those lines are not taking in the for each or even in the ref data internal table.

Can you help me with this ?

Thanks

Former Member
0 Kudos

Ok I recreate a new function type as a copy of formula but I check the parameters for initial value.

Thanks again Anass for your help !

Answers (0)