BI IP Average calculation

104 Views

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.

0CALMONTH ZIDFORM ZINTQUES ZINTREPO ZEVALUATION 05.2013 6156 Q003 R001 6 05.2013 6156 Q003 R002 14 05.2013 6156 Q003 R003 20 05.2013 6156 Q003 R004 6 05.2013 6156 Q004 R001 20 05.2013 6156 Q004 R002 20 05.2013 6156 Q004 R003 0 05.2013 6157 Q003 R001 0 05.2013 6157 Q003 R002 0 05.2013 6157 Q003 R003 0 05.2013 6157 Q003 R004 0 05.2013 6157 Q004 R001 6 05.2013 6157 Q004 R002 0

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.

0CALMONTH ZIDFORM ZINTQUES ZINTREPO ZEVALUATION 05.2013 6156 Q003 R001 6 05.2013 6156 Q003 R002 14 05.2013 6156 Q003 R003 20 05.2013 6156 Q003 R004 6 05.2013 6156 Q003 # 11,5 05.2013 6156 Q004 R001 20 05.2013 6156 Q004 R002 20 05.2013 6156 Q004 R003 0 05.2013 6156 Q004 # 13,33333333 05.2013 6157 Q003 R001 0 05.2013 6157 Q003 R002 0 05.2013 6157 Q003 R003 0 05.2013 6157 Q003 R004 0 05.2013 6157 Q003 # 0 05.2013 6157 Q004 R001 6 05.2013 6157 Q004 R002 0 05.2013 6157 Q004 R003 6 05.2013 6157 Q004 # 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.

If you have any suggestions, they will be welcomed.

Regards,

Jérôme