Help with Set Analysis

Mon, 28 Nov 2011 14:00:21 GMT

Hi There,

The following expression is used to calculate a value for each month (sPeriod).

We are battling to get the expression to calculate for a previous month(s) using set analysis.

We need to calculate the average of the total of the previous 3 months. If more than one Year/Month (sPeriod) is selected this expression must still hold true.

=sum(aggr(if((sum(
(
((( (
           if(F_TRNS_AMT <= TOBR_LOW_CASH_VALUE_LEVEL , TOBR_Low_CASH_Factor ,TOBR_Ideal_CASH_Factor ) *     F_CASH_TOBR_Units
     )
     +
     F_TOBR_Units) * TOBR_Unit_Reward ) * IF ( Accuracy_Amount_pm >= AccF_Step1_FROM AND   Accuracy_Amount_pm < AccF_Step2_FROM        ,        AccF_Step1_FACTOR        ,IF ( Accuracy_Amount_pm >= AccF_Step2_FROM AND   Accuracy_Amount_pm < AccF_Step3_FROM        ,        AccF_Step2_FACTOR        ,IF ( Accuracy_Amount_pm >= AccF_Step3_FROM AND   Accuracy_Amount_pm < AccF_Step4_FROM        ,        AccF_Step3_FACTOR        ,IF ( Accuracy_Amount_pm >= AccF_Step4_FROM AND   Accuracy_Amount_pm < AccF_Step5_FROM        ,        AccF_Step4_FACTOR        ,
     AccF_Step5_FACTOR        )))))
-
( Accuracy_Amount * AccFactorRATE)

) ) +

(sum( - AccFactorRATE * Accuracy_Amount )) )<0,0,(sum(

(
(
((( (
           if(F_TRNS_AMT <= TOBR_LOW_CASH_VALUE_LEVEL , TOBR_Low_CASH_Factor ,TOBR_Ideal_CASH_Factor ) *     F_CASH_TOBR_Units
     )
     +
     F_TOBR_Units) * TOBR_Unit_Reward ) * IF ( Accuracy_Amount_pm >= AccF_Step1_FROM AND   Accuracy_Amount_pm < AccF_Step2_FROM        ,        AccF_Step1_FACTOR        ,IF ( Accuracy_Amount_pm >= AccF_Step2_FROM AND   Accuracy_Amount_pm < AccF_Step3_FROM        ,        AccF_Step2_FACTOR        ,IF ( Accuracy_Amount_pm >= AccF_Step3_FROM AND   Accuracy_Amount_pm < AccF_Step4_FROM        ,        AccF_Step3_FACTOR        ,IF ( Accuracy_Amount_pm >= AccF_Step4_FROM AND   Accuracy_Amount_pm < AccF_Step5_FROM        ,        AccF_Step4_FACTOR        ,
     AccF_Step5_FACTOR        )))))
-
( Accuracy_Amount * AccFactorRATE)

)
)) +

(sum( - AccFactorRATE * Accuracy_Amount ))))

, EmpNumber_sPeriod))

Thanks

topic Help with Set Analysis in QlikView

Help with Set Analysis